Thread: RE: [SQL] Problems with default date and time

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

Sorry... I couldn't get the ord_time and ord_date fields
working either. =/

Will the ord_time/date/timestamp ever differ from
eachother? If not, I think you'll still be able to
do what you want to do even after removing the time
and date fields as these can be fetched from the
timestamp field.

/Kudo

> 
> -----Original Message-----
> From: Hutton, Rob
> Sent: Friday, August 13, 1999 10:25 AM
> To: 'Patrik Kudo'; Hutton, Rob
> Cc: 'pgsql-sql@postgresql.org'
> Subject: RE: [SQL] Problems with default date and time
> 
> The time and date fields are for reporting purposes while the
> timestamp is for calculating elapsed time, so I need all of them.
> When I make those changes, I get:
> 
> ERROR:  DEFAULT clause type 'timestamp' mismatched with column type
> 'time'
>