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

From Hutton, Rob
Subject RE: [SQL] Problems with default date and time
Date
Msg-id 11EFC736FB68D111B9DD00805FAD7C6D1E0C39@plymartpdc.internal.plymart.com
Whole thread Raw
List pgsql-sql
<p><font size="2">The problem is that I'm doing some reporting from crystal reports using the ODBC driver, and I need
tooffer a range to limit the dates that are printed.  Will this work with a datetime column?  </font><p><font
size="2">ex.</font><p><fontsize="2">Select * from table1 where datetimecolumn between 01/01/1999 and
01/15/1999</font><br/><p><font size="2">Rob</font><p><font size="2">-----Original Message-----</font><br /><font
size="2">From:Tom Lane [<a href="mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</a>]</font><br /><font
size="2">Sent:Friday, August 13, 1999 11:02 AM</font><br /><font size="2">To: Patrik Kudo</font><br /><font
size="2">Cc:Hutton, Rob; 'pgsql-sql@postgresql.org'</font><br /><font size="2">Subject: Re: [SQL] Problems with default
dateand time </font><br /><p><font size="2">Patrik Kudo <kudo@partitur.se> writes:</font><br /><font
size="2">>>"Hutton, Rob" wrote:</font><br /><font size="2">>> </font><br /><font size="2">>> I have
createda table with date and time fields by using what I</font><br /><font size="2">>> read as being the correct
defaultstatements, but I get the date and</font><br /><font size="2">>> time the DB was created at each insert
insteadof the current date and</font><br /><font size="2">>> time.</font><br /><font size="2">>> |
ord_time                        | time default text 'now'</font><br /><font size="2">>> |     8 |</font><br
/><fontsize="2">>> | ord_date                         | date default 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">> You should not use 'now'. It will
bereplaced with the current time.</font><br /><font size="2">> Instead use now() and remove "text".</font><p><font
size="2">The"default text 'now'" hack doesn't work with TIMESTAMP columns, only</font><br /><font size="2">with
DATETIMEcolumns --- this was reported last month.  I forget the</font><br /><font size="2">details but I think it is
triggeredby the presence of slightly</font><br /><font size="2">different sets of datatype conversion routines for the
twotypes in the</font><br /><font size="2">system tables, leading to a different path being taken that
evaluates</font><br/><font size="2">the default clause's value when it should not.  Probably a default of</font><br
/><fontsize="2">"now()" would fail for the same reason.  Fixing this is on the TODO</font><br /><font size="2">list,
butI do not think it is a trivial fix.</font><p><font size="2">In the meantime, I suggest using a DATETIME column ---
ortwo of them,</font><br /><font size="2">if you need the ability to record two different dates/times.</font><p>       
               <font size="2">regards, tom lane</font> 

pgsql-sql by date:

Previous
From: "Mike Field"
Date:
Subject: Multiple values for a field
Next
From: Chad Miller
Date:
Subject: err: select f() from i where (f()) in (select f() from x group by j);