Thread: RE: [SQL] Problems with default date 'now'

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

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