pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Jackson, DeJuan <djackson@cpsgroup.com>
To: pere@td.org.uit.no
To: pgsql-sql@postgreSQL.org
Subject: RE: [SQL] Problems with default date 'now'
Date: Wed, 10 Jun 1998 19:22:54 +0300
Message-ID: <l03110700b1a45fe2b70b@[147.233.159.109]> (raw)
In-Reply-To: <F10BB1FAF801D111829B0060971D839F2CDDC3@cpsmail>

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





view thread (7+ messages)

Message-ID: <l03110700b1a45fe2b70b@[147.233.159.109]>
Permalink:  ../l03110700b1a45fe2b70b@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03110700b1a45fe2b70b@[147.233.159.109]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, djackson@cpsgroup.com, pere@td.org.uit.no
  Subject: Re: [SQL] Problems with default date 'now'
  In-Reply-To: <l03110700b1a45fe2b70b@[147.233.159.109]>
  References: <F10BB1FAF801D111829B0060971D839F2CDDC3@cpsmail> <l03110700b1a45fe2b70b@[147.233.159.109]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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