date & time

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

How about...

select field1 where primary_key = select max(primary_key) from table;

or something like that :) I always mess up the parenthesis...cant remember
whether or not they are needed here around the second select.

-----Original Message-----
From: hoelc <hoelc@pd.jaring.my>
To: pgsql-interfaces@postgreSQL.org <pgsql-interfaces@postgreSQL.org>
Cc: pgsql-admin@postgreSQL.org <pgsql-admin@postgreSQL.org>;
pgsql-general@hub.org <pgsql-general@hub.org>
Date: Tuesday, March 09, 1999 7:03 AM
Subject: [GENERAL] the last row in a table

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

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

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. ____________

On Tue, Mar 09, 1999 at 08:08:43PM +0800, hoelc wrote:

I am developing a database with PostgreSQL in Linux, and using ecpg to write the
C program to communicate with the database.

Nice to hear that. :-)

One of my application need to get the data from the last row in a table.

Now that statement is sort of a problem. In a relational model there is no
order of tuples in a relation. You can simulate this via a unique key of
course or maybe use the OID. Or do you mean the last tuple of a query?

Is there any function or command that can tract which is the last row of data in
a table?

Once you opened a cursor you can move it to the end of the query via the
move command. I never used this feature but I take it this is how it is
supposed to be. Afterwards you can fetch one record backward.

Michael

--
Michael Meskes | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire!
Tel.: (+49) 2431/72651 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net | Use PostgreSQL!

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...

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              |
+-------------------------+--------------------------------------+