Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA84286 for ; Fri, 13 Aug 1999 11:04:15 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id LAA04541; Fri, 13 Aug 1999 11:02:25 -0400 (EDT) To: Patrik Kudo cc: "Hutton, Rob" , "'pgsql-sql@postgresql.org'" Subject: Re: [SQL] Problems with default date and time In-reply-to: Your message of Fri, 13 Aug 1999 16:04:36 +0200 <37B425F4.DF2F4037@partitur.se> Date: Fri, 13 Aug 1999 11:02:25 -0400 Message-ID: <4539.934556545@sss.pgh.pa.us> From: Tom Lane Patrik Kudo 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