RE: [SQL] Problems with default date and time - Mailing list pgsql-sql

<br /><p><font size="2">sorry, Here's the create statement.</font><p><font size="2">CREATE TABLE "orders" (</font><br
/><fontsize="2">        "ord_id" int4 DEFAULT nextval ( 'next_ord_id' ) NOT NULL,</font><br /><font size="2">       
"req_id"int4 DEFAULT nextval ( 'next_req_id' ) NOT NULL,</font><br /><font size="2">        "ord_description" character
varying(1500),</font><br/><font size="2">        "ord_priority" character varying(1500),</font><br /><font
size="2">       "ord_pri_order" int4,</font><br /><font size="2">        "ord_time" time DEFAULT now(),</font><br
/><fontsize="2">        "ord_date" date DEFAULT now(),</font><br /><font size="2">        "ord_timestamp" datetime
DEFAULTnow(),</font><br /><font size="2">        "ord_tech" character varying(1500),</font><br /><font size="2">       
"ord_stat"character varying(2),</font><br /><font size="2">        "ord_notes" character varying(1500),</font><br
/><fontsize="2">        "ord_whse" int4);</font><p><font size="2">-----Original Message-----</font><br /><font
size="2">From:Hutton, Rob </font><br /><font size="2">Sent: Friday, August 13, 1999 10:25 AM</font><br /><font
size="2">To:'Patrik Kudo'; Hutton, Rob</font><br /><font size="2">Cc: 'pgsql-sql@postgresql.org'</font><br /><font
size="2">Subject:RE: [SQL] Problems with default date and time</font><br /><p><font size="2">The time and date fields
arefor reporting purposes while the timestamp is for calculating elapsed time, so I need all of them.  When I make
thosechanges, I get:</font><p><font size="2">ERROR:  DEFAULT clause type 'timestamp' mismatched with column type
'time'</font><p><fontsize="2">-----Original Message-----</font><br /><font size="2">From: Patrik Kudo [<a
href="mailto:kudo@partitur.se">mailto:kudo@partitur.se</a>]</font><br/><font size="2">Sent: Friday, August 13, 1999
10:05AM</font><br /><font size="2">To: Hutton, Rob</font><br /><font size="2">Cc: 'pgsql-sql@postgresql.org'</font><br
/><fontsize="2">Subject: Re: [SQL] Problems with default date and time</font><br /><p><font size="2">> "Hutton, Rob"
wrote:</font><br/><font size="2">> </font><br /><font size="2">>   I have created a table with date and time
fieldsby using what I</font><br /><font size="2">> read as being the correct default statements, but I get the date
and</font><br/><font size="2">> time the DB was created at each insert instead of the current date and</font><br
/><fontsize="2">> time.</font><br /><font size="2">> | ord_time                         | time default text
'now'</font><br/><font size="2">> |     8 |</font><br /><font size="2">> | ord_date                         |
datedefault text 'now'</font><br /><font size="2">> |     4 |</font><br /><font size="2">> |
ord_timestamp                   | timestamp default text 'now'</font><br /><font size="2">> |     4 |</font><p><font
size="2">Youshould not use 'now'. It will be replaced with the current time.</font><br /><font size="2">Instead use
now()and remove "text".</font><p><font size="2">Also, I'd skip the time and date fields and exchange timestamp
with</font><br/><font size="2">datetime. You would still be able to get the date and time from the</font><br /><font
size="2">ord_timestampfield using:</font><p><font size="2">select ord_timestamp::time, ord_timestamp::date from
tablename;</font><p><fontsize="2">The reason I'd use datetime instead of datetime is because you</font><br /><font
size="2">can'tcast from timestamp to time (afaik).</font><p><font size="2">Hope this helps.</font><p><font
size="2">/Kudo</font>