pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problems with default date 'now'
7+ messages / 5 participants
[nested] [flat]

* Problems with default date 'now'
@ 1998-06-09 16:19  Petter Reinholdtsen <pere@td.org.uit.no>
  0 siblings, 2 replies; 7+ messages in thread

From: Petter Reinholdtsen @ 1998-06-09 16:19 UTC (permalink / raw)
  To: pgsql-sql


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-  <SCRIPT Language="Javascript">window.close()</SCRIPT>
http://www.hungry.com/~pere/    | Go Mozilla, go! Go!




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Problems with default date 'now'
@ 1998-06-10 06:49  Jerome ALET <alet@unice.fr>
  parent: Petter Reinholdtsen <pere@td.org.uit.no>
  1 sibling, 0 replies; 7+ messages in thread

From: Jerome ALET @ 1998-06-10 06:49 UTC (permalink / raw)
  To: Petter Reinholdtsen <pere@td.org.uit.no>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Problems with default date 'now'
@ 1998-06-10 11:43  Petter Reinholdtsen <pere@td.org.uit.no>
  0 siblings, 1 reply; 7+ messages in thread

From: Petter Reinholdtsen @ 1998-06-10 11:43 UTC (permalink / raw)
  To: pgsql-sql; +Cc: kenrsc@games.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-  <SCRIPT Language="Javascript">window.close()</SCRIPT>
http://www.hungry.com/~pere/    | Go Mozilla, go! Go!




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Problems with default date 'now'
@ 1998-06-10 12:09  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: Petter Reinholdtsen <pere@td.org.uit.no>
  0 siblings, 0 replies; 7+ messages in thread

From: Herouth Maoz @ 1998-06-10 12:09 UTC (permalink / raw)
  To: pere@td.org.uit.no; pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Problems with default date 'now'
@ 1998-06-10 12:50  Jose' Soares Da Silva <sferac@bo.nettuno.it>
  parent: Petter Reinholdtsen <pere@td.org.uit.no>
  1 sibling, 0 replies; 7+ messages in thread

From: Jose' Soares Da Silva @ 1998-06-10 12:50 UTC (permalink / raw)
  To: Petter Reinholdtsen <pere@td.org.uit.no>; pere@td.org.uit.no; +Cc: pgsql-sql

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'




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: [SQL] Problems with default date 'now'
@ 1998-06-10 15:14  Jackson, DeJuan <djackson@cpsgroup.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Jackson, DeJuan @ 1998-06-10 15:14 UTC (permalink / raw)
  To: Herouth Maoz <herouth@oumail.openu.ac.il>; pere@td.org.uit.no; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: [SQL] Problems with default date 'now'
@ 1998-06-10 16:22  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: Jackson, DeJuan <djackson@cpsgroup.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Herouth Maoz @ 1998-06-10 16:22 UTC (permalink / raw)
  To: Jackson, DeJuan <djackson@cpsgroup.com>; pere@td.org.uit.no; pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~1998-06-10 16:22 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-06-09 16:19 Problems with default date 'now' Petter Reinholdtsen <pere@td.org.uit.no>
1998-06-10 06:49 ` Jerome ALET <alet@unice.fr>
1998-06-10 12:50 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
1998-06-10 11:43 Re: [SQL] Problems with default date 'now' Petter Reinholdtsen <pere@td.org.uit.no>
1998-06-10 12:09 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-06-10 15:14 RE: [SQL] Problems with default date 'now' Jackson, DeJuan <djackson@cpsgroup.com>
1998-06-10 16:22 ` Herouth Maoz <herouth@oumail.openu.ac.il>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox