Horton

Thread

  1. date & time

    hoelc <hoelc@pd.jaring.my> — 1999-03-06T17:20:23Z

    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

  2. Re: [GENERAL] date & time

    Kevin Heflin <kheflin@shreve.net> — 1999-03-06T17:25:48Z

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

  3. Re: [GENERAL] date & time

    James Thompson <jamest@math.ksu.edu> — 1999-03-06T20:03:03Z

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

  4. Re: [ADMIN] date & time

    Anatoly K. Lasareff <tolik@icomm.ru> — 1999-03-07T09:56:05Z

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

  5. the last row in a table

    hoelc <hoelc@pd.jaring.my> — 1999-03-09T12:08:43Z

    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

  6. Re: [INTERFACES] the last row in a table

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-03-09T15:20:14Z

    > 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

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

    Dustin Sallings <dustin@spy.net> — 1999-03-09T19:24:15Z

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

  8. Re: [INTERFACES] the last row in a table

    Michael Meskes <meskes@postgresql.org> — 1999-03-09T19:30:57Z

    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!

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

    Marcin Grondecki <ojciec@mtl.pl> — 1999-03-10T02:32:51Z

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

  10. Re: [GENERAL] the last row in a table

    Oleg Broytmann <phd@sun.med.ru> — 1999-03-10T08:42:06Z

    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.

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

    Stuart Rison <stuart@ludwig.ucl.ac.uk> — 1999-03-10T11:38:39Z

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