Thread: Problems with default date and time
<p><font size="2"> I have created a table with date and time fields by using what I read as being the correct default statements,but I get the date and time the DB was created at each insert instead of the current date and time.</font><p><fontsize="2">+----------------------------------+----------------------------------+-------+</font><br /><fontsize="2">| Field | Type | Length|</font><br /><font size="2">+----------------------------------+----------------------------------+-------+</font><br/><font size="2">| ord_id | int4 not null default nextval ( | 4 |</font><br /><font size="2">| req_id | int4 not null default nextval ( | 4 |</font><br /><font size="2">| ord_description | varchar() | 1500 |</font><br /><font size="2">| ord_priority | varchar() | 1500 |</font><br /><font size="2">| ord_pri_order | int4 | 4 |</font><br /><font size="2">| ord_time | time default text 'now' | 8 |</font><br /><font size="2">| ord_date | date default text 'now' | 4 |</font><br /><font size="2">| ord_timestamp | timestamp default text 'now' | 4 |</font><br /><font size="2">| ord_tech | varchar() | 1500 |</font><br /><font size="2">| ord_stat | varchar() | 2 |</font><br /><font size="2">| ord_notes | varchar() | 1500 |</font><br /><font size="2">| ord_whse | int4 | 4 |</font><br /><font size="2">+----------------------------------+----------------------------------+-------+</font><br/><p><font size="2">Theinsert statements for the following records were executed about 30 seconds apart.</font><p><font size="2">|09:34:22|08-13-1999|1999-08-1309:34:22-04|test3 | | | 96| 95| | |</font><br/><font size="2">|09:34:22|08-13-1999|1999-08-13 09:34:22-04|test4 | | | 97| 96| | |</font><br /><font size="2">|09:34:22|08-13-1999|1999-08-13 09:34:22-04|test4 | | |</font><br /><p><font size="2">[PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66] on Redhat Linux6.0, Kernel 2.2.10</font><p><font size="2">Thanks in advance for the help,</font><br /><font size="2">Rob</font>
> "Hutton, Rob" wrote: > > I have created a table with date and time fields by using what I > read as being the correct default statements, but I get the date and > time the DB was created at each insert instead of the current date and > time. > | ord_time | time default text 'now' > | 8 | > | ord_date | date default text 'now' > | 4 | > | ord_timestamp | timestamp default text 'now' > | 4 | You should not use 'now'. It will be replaced with the current time. Instead use now() and remove "text". Also, I'd skip the time and date fields and exchange timestamp with datetime. You would still be able to get the date and time from the ord_timestamp field using: select ord_timestamp::time, ord_timestamp::date from tablename; The reason I'd use datetime instead of datetime is because you can't cast from timestamp to time (afaik). Hope this helps. /Kudo
Patrik Kudo <kudo@partitur.se> writes:
>> "Hutton, Rob" wrote:
>>
>> I have created a table with date and time fields by using what I
>> read as being the correct default statements, but I get the date and
>> time the DB was created at each insert instead of the current date and
>> time.
>> | ord_time | time default text 'now'
>> | 8 |
>> | ord_date | date default text 'now'
>> | 4 |
>> | ord_timestamp | timestamp default text 'now'
>> | 4 |
> You should not use 'now'. It will be replaced with the current time.
> Instead use now() and remove "text".
The "default text 'now'" hack doesn't work with TIMESTAMP columns, only
with DATETIME columns --- this was reported last month. I forget the
details but I think it is triggered by the presence of slightly
different sets of datatype conversion routines for the two types in the
system tables, leading to a different path being taken that evaluates
the default clause's value when it should not. Probably a default of
"now()" would fail for the same reason. Fixing this is on the TODO
list, but I do not think it is a trivial fix.
In the meantime, I suggest using a DATETIME column --- or two of them,
if you need the ability to record two different dates/times.
regards, tom lane