Обсуждение: date & time
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
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
--------------------------------------------------------------------
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
->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
>>>>> "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
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
> 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
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...
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. ____________
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.
>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 | +-------------------------+--------------------------------------+