From pere@td.org.uit.no Tue Jun 9 16:20:19 1998 Received: from yuubin.games.no (yuubin.link.no [193.213.36.89]) by hub.org (8.8.8/8.7.5) with ESMTP id MAA17645 for ; Tue, 9 Jun 1998 12:20:19 -0400 (EDT) Received: from sleeper.games.no (pere@sleeper.link.no [193.213.36.74]) by yuubin.games.no (8.8.7/8.8.7) with ESMTP id SAA07523; Tue, 9 Jun 1998 18:21:04 +0200 Received: (from pere@localhost) by sleeper.games.no (8.8.7/8.8.7) id SAA10905; Tue, 9 Jun 1998 18:19:49 +0200 Date: Tue, 9 Jun 1998 18:19:49 +0200 Message-Id: <199806091619.SAA10905@sleeper.games.no> To: pgsql-sql@postgresql.org Reply-to: Petter Reinholdtsen Subject: Problems with default date 'now' From: Petter Reinholdtsen Reply-to: pere@td.org.uit.no I have a table where I want the default value of a date field to be todays date when I do insert. I have this table definition: CREATE TABLE testdate( received date DEFAULT 'now', info text NOT NULL ); The problem is that the 'now' apparently is substituted when I create the table, not when I insert into it. Is there another way to handle this? This is on Linux RedHat 5.0 with PostgreSQL 6.2.1. Please remember to send replies to me (too), as I am not member on this mailing-list. Happy hacking, -- ##> Petter Reinholdtsen <## | pere@td.org.uit.no O- http://www.hungry.com/~pere/ | Go Mozilla, go! Go! From alet@unice.fr Wed Jun 10 06:56:00 1998 Received: from cortex.unice.fr (root@cortex.unice.fr [134.59.53.1]) by hub.org (8.8.8/8.7.5) with ESMTP id CAA09094 for ; Wed, 10 Jun 1998 02:56:00 -0400 (EDT) Received: from unice.fr (cortex.unice.fr [134.59.53.1]) by cortex.unice.fr (8.8.5/8.8.5) with ESMTP id IAA12343; Wed, 10 Jun 1998 08:50:00 +0200 Sender: jerome@cortex.unice.fr Message-ID: <357E2C97.7FB98C3C@unice.fr> Date: Wed, 10 Jun 1998 06:49:59 +0000 From: Jerome ALET Reply-To: alet@unice.fr Organization: Faculte de Medecine de Nice X-Mailer: Mozilla 4.04 [en] (X11; I; Linux 2.0.33 sparc) MIME-Version: 1.0 To: Petter Reinholdtsen CC: pgsql-sql@postgresql.org Subject: Re: [SQL] Problems with default date 'now' References: <199806091619.SAA10905@sleeper.games.no> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Petter Reinholdtsen wrote: > > I have a table where I want the default value of a date field to be > todays date when I do insert. I have this table definition: > > CREATE TABLE testdate( > received date DEFAULT 'now', > info text NOT NULL > ); > > The problem is that the 'now' apparently is substituted when I create > the table, not when I insert into it. Is there another way to handle > this? This is on Linux RedHat 5.0 with PostgreSQL 6.2.1. > replace your field definition with this one: received_date DATE DEFAULT CURRENT_DATE it works fine. bye, Jerome ALET - alet@unice.fr - http://cortex.unice.fr/~jerome Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30 28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE From pere@td.org.uit.no Wed Jun 10 11:43:49 1998 Received: from yuubin.games.no (yuubin.link.no [193.213.36.89]) by hub.org (8.8.8/8.7.5) with ESMTP id HAA10928 for ; Wed, 10 Jun 1998 07:43:49 -0400 (EDT) Received: from sleeper.games.no (pere@sleeper.link.no [193.213.36.74]) by yuubin.games.no (8.8.7/8.8.7) with ESMTP id NAA09626; Wed, 10 Jun 1998 13:44:42 +0200 Received: (from pere@localhost) by sleeper.games.no (8.8.7/8.8.7) id NAA11630; Wed, 10 Jun 1998 13:43:25 +0200 Date: Wed, 10 Jun 1998 13:43:25 +0200 Message-Id: <199806101143.NAA11630@sleeper.games.no> To: pgsql-sql@postgresql.org Cc: kenrsc@games.no Subject: Re: [SQL] Problems with default date 'now' From: Petter Reinholdtsen Reply-to: pere@td.org.uit.no > replace your field definition with this one: > > received_date DATE DEFAULT CURRENT_DATE > > it works fine. Nope, that did not work. I get this reply from psql: WARN:parser: parse error at or near "current_date" What is wrong. Is this something new in PostgreSQL after v6.2.1? I don't follow the SQL mailinglist, so please send me a copy when you reply. -- ##> Petter Reinholdtsen <## | pere@td.org.uit.no O- http://www.hungry.com/~pere/ | Go Mozilla, go! Go! From herouth@oumail.openu.ac.il Wed Jun 10 12:13:38 1998 Received: from shaked.cc.openu.ac.il (shaked.cc.openu.ac.il [147.233.145.65]) by hub.org (8.8.8/8.7.5) with ESMTP id IAA14991 for ; Wed, 10 Jun 1998 08:13:38 -0400 (EDT) Received: from [147.233.159.109] (herouth@mac-herouth.pc.openu.ac.il [147.233.159.109]) by shaked.cc.openu.ac.il (8.8.5/8.8.5) with ESMTP id PAA02442; Wed, 10 Jun 1998 15:09:50 +0300 (IDT) X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: <199806101143.NAA11630@sleeper.games.no> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Wed, 10 Jun 1998 15:09:37 +0300 To: pere@td.org.uit.no, pgsql-sql@postgreSQL.org From: Herouth Maoz Subject: Re: [SQL] Problems with default date 'now' At 14:43 +0300 on 10/6/98, Petter Reinholdtsen wrote: > Nope, that did not work. > > I get this reply from psql: > > WARN:parser: parse error at or near "current_date" > > What is wrong. Is this something new in PostgreSQL after v6.2.1? Yes, it's something new. I still work with 6.2.1, so here's the deal: Using a constant default value for a column causes the constant to be evaluated once, at the creation of the table. That value is then kept with the table schema, which means each row will be stamped with the same date. In order to avoid that, you have to use a function as a default value. Functions are evaluated each time a column is created. For this purpose, I created an SQL function like this: CREATE FUNCTION current_datetime() RETURNS datetime AS 'SELECT ''now''::datetime' LANGUAGE 'sql'; And I define the table as (in my case): CREATE TABLE session ( session int4 DEFAULT nextval( 'sess_no' ) NOT NULL, created datetime DEFAULT current_datetime() -- See here NOT NULL, webuser char(30) ); You can define the function once, and use it for all the applications using the same database. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma From sferac@bo.nettuno.it Wed Jun 10 11:33:39 1998 Received: from proxy.bazzanese.com (root@ip179.tol11.interbusiness.it [195.120.106.179]) by hub.org (8.8.8/8.7.5) with ESMTP id HAA09749 for ; Wed, 10 Jun 1998 07:33:39 -0400 (EDT) Received: from localhost (sferac@localhost) by proxy.bazzanese.com (8.8.8/8.8.8/Debian/GNU) with SMTP id MAA03662; Wed, 10 Jun 1998 12:50:18 GMT Date: Wed, 10 Jun 1998 12:50:18 +0000 (UTC) From: "Jose' Soares Da Silva" X-Sender: sferac@proxy.bazzanese.com To: Petter Reinholdtsen , pere@td.org.uit.no cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Problems with default date 'now' In-Reply-To: <199806091619.SAA10905@sleeper.games.no> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII On Tue, 9 Jun 1998, Petter Reinholdtsen wrote: > > I have a table where I want the default value of a date field to be > todays date when I do insert. I have this table definition: > > CREATE TABLE testdate( > received date DEFAULT 'now', > info text NOT NULL > ); > CREATE TABLE testdate( received date DEFAULT CURRENT_DATE, info text NOT NULL ); > The problem is that the 'now' apparently is substituted when I create > the table, not when I insert into it. Is there another way to handle > this? This is on Linux RedHat 5.0 with PostgreSQL 6.2.1. > Jose' From djackson@cpsgroup.com Wed Jun 10 15:14:33 1998 Received: from bjke.com (firewall-user@[144.210.8.38]) by hub.org (8.8.8/8.7.5) with ESMTP id LAA05684 for ; Wed, 10 Jun 1998 11:14:33 -0400 (EDT) Received: by bjke.com; id LAA19097; Wed, 10 Jun 1998 11:12:06 -0400 (EDT) Received: from cpsmail.cpsgroup.com(144.210.12.11) by gauntlet.bjke.com via smap (3.2) id xmab18666; Wed, 10 Jun 98 11:11:32 -0400 Received: by cpsmail with Internet Mail Service (5.0.1458.49) id ; Wed, 10 Jun 1998 10:14:44 -0500 Message-ID: From: "Jackson, DeJuan" To: Herouth Maoz , pere@td.org.uit.no, pgsql-sql@postgreSQL.org Subject: RE: [SQL] Problems with default date 'now' Date: Wed, 10 Jun 1998 10:14:44 -0500 X-Priority: 3 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.0.1458.49) Content-Type: text/plain > > Nope, that did not work. > > > > I get this reply from psql: > > > > WARN:parser: parse error at or near "current_date" > > > > What is wrong. Is this something new in PostgreSQL after v6.2.1? > > Yes, it's something new. I still work with 6.2.1, so here's the deal: > > Using a constant default value for a column causes the constant to be > evaluated once, at the creation of the table. That value is then kept > with > the table schema, which means each row will be stamped with the same > date. > > In order to avoid that, you have to use a function as a default value. > Functions are evaluated each time a column is created. For this > purpose, I > created an SQL function like this: > > CREATE FUNCTION current_datetime() RETURNS datetime > AS 'SELECT ''now''::datetime' > LANGUAGE 'sql'; > > And I define the table as (in my case): > > CREATE TABLE session > ( > session int4 > DEFAULT nextval( 'sess_no' ) > NOT NULL, > created datetime > DEFAULT current_datetime() -- See here > NOT NULL, > webuser char(30) > ); > > You can define the function once, and use it for all the applications > using > the same database. > > Herouth > Why don't you just use the function version of now (I'm not familiar with 6.2.1 so it could be that it doesn't exist). received_date DATE DEFAULT NOW() just a thought, -DEJ From herouth@oumail.openu.ac.il Wed Jun 10 16:26:27 1998 Received: from shaked.cc.openu.ac.il (shaked.cc.openu.ac.il [147.233.145.65]) by hub.org (8.8.8/8.7.5) with ESMTP id MAA19802 for ; Wed, 10 Jun 1998 12:26:27 -0400 (EDT) Received: from [147.233.159.109] (herouth@mac-herouth.pc.openu.ac.il [147.233.159.109]) by shaked.cc.openu.ac.il (8.8.5/8.8.5) with ESMTP id TAA04836; Wed, 10 Jun 1998 19:22:53 +0300 (IDT) X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Wed, 10 Jun 1998 19:22:54 +0300 To: "Jackson, DeJuan" , pere@td.org.uit.no, pgsql-sql@postgreSQL.org From: Herouth Maoz Subject: RE: [SQL] Problems with default date 'now' At 18:14 +0300 on 10/6/98, Jackson, DeJuan wrote: > > > Why don't you just use the function version of now (I'm not familiar > with 6.2.1 so it could be that it doesn't exist). > > received_date DATE DEFAULT NOW() As I understand it, date is not the same as datetime, and datetime is the most precise and wide-ranging of PostgreSQL various date and time types (too many by half, if you ask me). It also has most of the functions. Well, you say, why not just convert now() into datetime (because it's of type date, and undocumented in the pgbuiltin page, at least in version 6.2.1). Well, the reason lies here: testing=> select datetime(now()), 'now'::datetime; datetime |?column? ----------------------------+---------------------------- Wed Jun 10 22:03:54 1998 IDT|Wed Jun 10 19:03:54 1998 IDT (1 row) The time was 19:03:54. Why three hours? Because IDT is GMT+3. This is an old problem, in which certain timezones lose their differential from GMT in the conversion between date and datetime. Ahem. Another solution suggested in the past was datetime( 'now'::text ), since the datetime conversion is a function. Well, this is what it does: testing=> CREATE TABLE tst1 ( testing-> stamp datetime DEFAULT datetime( 'now'::text ) NOT NULL testing-> ); PQexec() -- Request was sent to backend, but backend closed the channel before responding. This probably means the backend terminated abnormally before or while processing the request. Ahem, ahem. The reason for this one is that the backend cannot handle the explicit type cast (::text) in an argument to a function. In short, I found a way, it works around all the known bugs in 6.2.1, so that's the way I use until I can upgrade to 6.3.2. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma