Thread: date & time

date & time

From

hoelc

Date:

06 March 1999, 15:20:04

Hello,
Can some one please tell me how to set the date&time in the PostgreSQL
system?
I try to use date('now') and time('now') to keep tract of the data and
time when the data is inserted or updated.  When I use sql " insert into
table (data1, date_chg, time_chg) values ('abc',date('now'),time('now'))
" to insert the date and time data, the data successfully inserted but
when I retrive the data, it shows that the date and time is always "
01-01-2000 " and " 08:00:00 " , now is of couse not year 2000 and the
time is also not 8 o'clock.  Why?  How should I correct this?
I am using PostgreSQL in Linux system, and the date & time for Linux
system are correct.

One more question, what is the common data type for storing address?

Thank you very much.
Regards .... lch

Re: [GENERAL] date & time

From

Kevin Heflin

Date:

06 March 1999, 15:25:58

On Sun, 7 Mar 1999, hoelc wrote:

> Hello,
> Can some one please tell me how to set the date&time in the PostgreSQL
> system?
> I try to use date('now') and time('now') to keep tract of the data and
> time when the data is inserted or updated.  When I use sql " insert into
> table (data1, date_chg, time_chg) values ('abc',date('now'),time('now'))
> " to insert the date and time data, the data successfully inserted but
> when I retrive the data, it shows that the date and time is always "
> 01-01-2000 " and " 08:00:00 " , now is of couse not year 2000 and the
> time is also not 8 o'clock.  Why?  How should I correct this?
> I am using PostgreSQL in Linux system, and the date & time for Linux
> system are correct.


I'm not real sure, but I would try CURRENT_DATE or CURRENT_DATETIME ?

Kevin




--------------------------------------------------------------------
Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
VP/Mac Tech           | 333 Texas St #619    | FAX:318.221.6612
kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------

Re: [GENERAL] date & time

From

James Thompson

Date:

06 March 1999, 18:03:07

On Sun, 7 Mar 1999, hoelc wrote:

> Hello,
> Can some one please tell me how to set the date&time in the PostgreSQL
> system?
> I try to use date('now') and time('now') to keep tract of the data and
> time when the data is inserted or updated.  When I use sql " insert into
> table (data1, date_chg, time_chg) values ('abc',date('now'),time('now'))
> " to insert the date and time data, the data successfully inserted but
> when I retrive the data, it shows that the date and time is always "
> 01-01-2000 " and " 08:00:00 " , now is of couse not year 2000 and the
> time is also not 8 o'clock.  Why?  How should I correct this?
> I am using PostgreSQL in Linux system, and the date & time for Linux
> system are correct.
>

I use date(now()) and it seems to work ok for me.  Until I added the ()
after now it was always some fixed date just like you have.  Never tried
it with time.

->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
James Thompson    138 Cardwell Hall  Manhattan, Ks   66506    785-532-0561
Kansas State University                          Department of Mathematics
->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<

Re: [ADMIN] date & time

From

tolik@icomm.ru (Anatoly K. Lasareff)

Date:

07 March 1999, 08:03:04

>>>>> "h" == hoelc  <hoelc@pd.jaring.my> writes:

 h> Hello,
 h> Can some one please tell me how to set the date&time in the PostgreSQL
 h> system?
 h> I try to use date('now') and time('now') to keep tract of the data and
 h> time when the data is inserted or updated.  When I use sql " insert into
 h> table (data1, date_chg, time_chg) values ('abc',date('now'),time('now'))
 h> " to insert the date and time data, the data successfully inserted but
 h> when I retrive the data, it shows that the date and time is always "
 h> 01-01-2000 " and " 08:00:00 " , now is of couse not year 2000 and the
 h> time is also not 8 o'clock.  Why?  How should I correct this?
 h> I am using PostgreSQL in Linux system, and the date & time for Linux
 h> system are correct.

Yes, here is the problem:

tolik=> select date('now'), time('now');
      date|time
----------+--------
01-01-2000|03:00:00
(1 row)


Here is the solution:

tolik=> select date('now'::datetime), time('now'::datetime);
      date|time
----------+--------
03-07-1999|13:00:55
(1 row)

--
Anatoly K. Lasareff              Email:       tolik@icomm.ru
Senior programmer

the last row in a table

From

hoelc

Date:

09 March 1999, 10:08:56

Hello,
I am developing a database with PostgreSQL in Linux, and using ecpg to write the
C program to communicate with the database.
One of my application need to get the data from the last row in a table.
Is there any function or command that can tract which is the last row of data in
a table?
Thank you.
Regards,
lch

Re: [INTERFACES] the last row in a table

From

"Thomas G. Lockhart"

Date:

09 March 1999, 13:20:29

> I am developing a database with PostgreSQL in Linux, and using ecpg to
> write the C program to communicate with the database.
> One of my application need to get the data from the last row in a
> table. Is there any function or command that can tract which is the
> last row of data in a table?

SQL is a set-oriented language. One should probably not assign any
significance to a storage order of rows.

It *is* possible to order the results of a query, and then your
first/last qualities do have meaning. I would suggest ordering your
query (perhaps on a "row update time" if by "the last row" you mean "the
more recently entered row") using the "DESC" qualifier in the ORDER BY
clause, then use a cursor to pick up the first row returned.

Good luck.

                     - Tom

Re: [GENERAL] Re: [INTERFACES] the last row in a table

From

Marcin Grondecki

Date:

09 March 1999, 15:42:13

At 15:20 99-03-09 +0000, you wrote:
>> I am developing a database with PostgreSQL in Linux, and using ecpg to
>> write the C program to communicate with the database.
>> One of my application need to get the data from the last row in a
>> table. Is there any function or command that can tract which is the
>> last row of data in a table?
>
>SQL is a set-oriented language. One should probably not assign any
>significance to a storage order of rows.
>
>It *is* possible to order the results of a query, and then your
>first/last qualities do have meaning. I would suggest ordering your
>query (perhaps on a "row update time" if by "the last row" you mean "the
>more recently entered row") using the "DESC" qualifier in the ORDER BY
>clause, then use a cursor to pick up the first row returned.
>
There is better answer, i think. Simply read smth about cursors - declaring,
using, droping. I think this is what u need.
In case of troubles with understanding, i'd serve with some examples in Perl.

Marcin Grondecki
ojciec@mtl.pl
+48(604)468725
***** I'm not a complete idiot, some parts are missing...

Re: [GENERAL] Re: [INTERFACES] the last row in a table

From

Dustin Sallings

Date:

09 March 1999, 17:30:17

On Tue, 9 Mar 1999, Marcin Grondecki wrote:

# There is better answer, i think. Simply read smth about cursors -
# declaring, using, droping. I think this is what u need.  In case of
# troubles with understanding, i'd serve with some examples in Perl.

    If it's the exact last input row you want, a trigger or rule would
be still yet better.  :)

--
SA, beyond.com           My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________

Re: [GENERAL] the last row in a table

From

Oleg Broytmann

Date:

10 March 1999, 06:43:08

Hi!

On Tue, 9 Mar 1999, hoelc wrote:
> I am developing a database with PostgreSQL in Linux, and using ecpg to write the
> C program to communicate with the database.
> One of my application need to get the data from the last row in a table.
> Is there any function or command that can tract which is the last row of data in
> a table?

   Reorder your query to get the data in the FIRST row and use cursor to fetch
just the row.

> Thank you.
> Regards,
> lch

Oleg.
----
    Oleg Broytmann  National Research Surgery Centre  http://sun.med.ru/~phd/
           Programmers don't die, they just GOSUB without RETURN.

Re: [GENERAL] Re: [INTERFACES] the last row in a table

From

Stuart Rison

Date:

10 March 1999, 09:52:15

>On Tue, 9 Mar 1999, Marcin Grondecki wrote:
>
># There is better answer, i think. Simply read smth about cursors -
># declaring, using, droping. I think this is what u need.  In case of
># troubles with understanding, i'd serve with some examples in Perl.
>
>    If it's the exact last input row you want, a trigger or rule would
>be still yet better.  :)

Or another possibility (again, if it's the last inputed row you want) is

SELECT * FROM <table> WHERE oid=( SELECT max(oid::float) FROM <table> );

I'm assuming here that the largest the oid the latest it was put in...
that's right isn't it (he said, all of a sudden hesitant).

Also, I could only get this to work by typecasting oid as float.  Anybody
know why?

Regards,

Stuart.


+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+