Thread: Problems with default date and time

Problems with default date and time

From
"Hutton, Rob"
Date:
<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> 

Re: [SQL] Problems with default date and time

From
Patrik Kudo
Date:
> "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


Re: [SQL] Problems with default date and time

From
Tom Lane
Date:
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