Re: [SQL] Date
От | Chairudin Sentosa Harjo |
---|---|
Тема | Re: [SQL] Date |
Дата | |
Msg-id | 35A5A731.50FABE98@dnet.net.id обсуждение исходный текст |
Список | pgsql-sql |
Thank you for all your helps. The 'datetime' is indeed a better choice. The 'now' is the answer for my question. Now, I have one question, that I think it is wierd. create table blah ( custnum int, startdate datetime default datetime(now()), enddate datetime ); FIRST: insert into blah (custnum, enddate) values (1,'01-JAN-00'); SECOND: insert into blah (custnum, enddate) values (1,'01-01-00'); THIRD: insert into blah (custnum, enddate) values (1,'01-01-2000'); FOUR: insert into blah (custnum, enddate) values (custnum, '01012000'); The first, second and third does the right thing, which is tyring to insert 01 JANUARY 2000, the result in postgres is : Sat Jan 01 00:00:00 2000 However, the fourth is wrong, the result in postgres is : Wed Aug 02 00:00:00 0102 Could someone tell me why? Regards Chai Aleksey Dashevsky wrote: > On Wed, 8 Jul 1998, Chairudin Sentosa Harjo wrote: > > > Hello, > > > > I need help with date. > > > > create table blah > > ( > > custnum int, > > startdate date, > > enddate date > > ); > > > > How do I get the "real date" to insert to startdate when > > the data is entered? > > > > insert into blah > > (custnum, startdate, enddate) > > values > > (001,???,'NULL'); > > > > I need a way to fill in the '???' field. > > In oracle I could use > > select sysdate from dual; > > There are some different ways: > 1. You can try to use current_date and current_time system varibales. > 2. You can use constant 'now' casted to appropriate data type, e.g. : > template1=> select 'now'::date; > ?column? > ---------- > 08-07-1998 > (1 row) > > template1=> select 'now'::datetime; > ?column? > ---------------------------- > Wed 08 Jul 12:39:22 1998 IDT > (1 row) > > template1=> select 'now'::time; > ?column? > -------- > 12:39:25 > (1 row) > > 3. There is also now() function: > > template1=> select now(); > now > ---------------------- > 1998-07-08 12:39:47+03 > (1 row) > > It is almost the same as constant 'now', but if you want to create table > which will have auto-timestamp-field, you need following syntax: > > proba=> create table proba (i int, tt datetime default datetime(now())); > CREATE > > proba=> insert into proba values (1); > INSERT 2170370 1 > proba=> insert into proba values (2); > INSERT 2170371 1 > proba=> insert into proba values (7); > INSERT 2170372 1 > proba=> insert into proba values (989); > INSERT 2170373 1 > proba=> select * from proba; > i|tt > ---+---------------------------- > 1|Wed 08 Jul 12:54:53 1998 IDT > 2|Wed 08 Jul 12:54:56 1998 IDT > 7|Wed 08 Jul 12:54:58 1998 IDT > 989|Wed 08 Jul 12:55:02 1998 IDT > (4 rows)
В списке pgsql-sql по дате отправления: