pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [INTERFACES] where did that date and time come from??
4+ messages / 3 participants
[nested] [flat]
* Re: [INTERFACES] where did that date and time come from??
@ 1999-04-20 00:07 ` Screech <screech@screech.org>
1 sibling, 0 replies; 4+ messages in thread
From: Screech @ 1999-04-20 00:07 UTC (permalink / raw)
To: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>; +Cc: 'pgsql-admin@hub.org' <pgsql-admin@hub.org>; 'pgsql-interfaces@hub.org' <pgsql-interfaces@hub.org>
On Mon, 19 Apr 1999, JT Kirkpatrick wrote:
> Whoa baby, I lied through my teeth! Inserting DID NOT work fine when done
> in psql on the server itself - it also returned the april 14 date and time
> as default. So I tested around a bit. I redefined a new test table using
> default now() and guess what - it works!!!!! But the documentation says
> that I can define fields as default 'now'. doesn't seem to work right as
> far as I can tell, but defining them as default now() does. . .
I'm just guessing here, but I think I saw somewhere in the Postgres dox
that said if something weren't declared correctly, that it would be
interpreted as a constant and not a function call. If my memory serves
me, this would mean that SQL was actually parsed as default 'apr 14,
10am.' Could be wrong. I've done a lot of reading recently, and, for all
I know, that could be an Oracle quirk. :P
Caveat emptor...
--
Jason Lee | for all my labors and best laid plans I'd only earned
www.screech.org | a reprimand, forevermore to understand that dreams come
screech@screech.org | true can kill a man if never graced by sovereign hands
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] where did that date and time come from??
@ 1999-04-20 14:40 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-04-23 09:13 ` Re: [INTERFACES] where did that date and time come from?? Chairudin Sentosa <chairudin@prima.net.id>
1 sibling, 1 reply; 4+ messages in thread
From: Thomas Lockhart @ 1999-04-20 14:40 UTC (permalink / raw)
To: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>; +Cc: 'pgsql-admin@hub.org' <pgsql-admin@hub.org>; 'pgsql-interfaces@hub.org' <pgsql-interfaces@hub.org>
> I have a database that has four fields -
> Create table dispnote (
> Autoinv int4
> noteby char(8) not null default 'current_user',
> added datetime not null default 'now',
> notes varchar(255),
> constraint dispnotepk primary key (autoinv, added),
> constraint dispnotessms foreign key (auoinv) references ssms
> );
> ... But when I insert a record it came up with a date of april 14, 10am! My
> server and my client both have the correct date and time (april 19, 3pm) -
> where did it get the wrong date and time from??
This is (still) a problem with Postgres optimizing untyped constants
by evaluating them at table creation time rather than at run time.
Redefine your datetime field as
...
added datetime not null default text 'now',
...
to force the type of the default value to be a string, which is then
converted at run time as you would expect. Fixing this behavior would
seem to be a good project for someone (maybe me :) for v6.6...
- Tom
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] where did that date and time come from??
1999-04-20 14:40 ` Re: [INTERFACES] where did that date and time come from?? Thomas Lockhart <lockhart@alumni.caltech.edu>
@ 1999-04-23 09:13 ` Chairudin Sentosa <chairudin@prima.net.id>
1999-04-23 05:21 ` Re: [INTERFACES] where did that date and time come from?? Thomas Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Chairudin Sentosa @ 1999-04-23 09:13 UTC (permalink / raw)
To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>; 'pgsql-admin@hub.org' <pgsql-admin@hub.org>; 'pgsql-interfaces@hub.org' <pgsql-interfaces@hub.org>
Thomas Lockhart wrote:
> > I have a database that has four fields -
> > Create table dispnote (
> > Autoinv int4
> > noteby char(8) not null default 'current_user',
> > added datetime not null default 'now',
> > notes varchar(255),
> > constraint dispnotepk primary key (autoinv, added),
> > constraint dispnotessms foreign key (auoinv) references ssms
> > );
> > ... But when I insert a record it came up with a date of april 14, 10am! My
> > server and my client both have the correct date and time (april 19, 3pm) -
> > where did it get the wrong date and time from??
>
> This is (still) a problem with Postgres optimizing untyped constants
> by evaluating them at table creation time rather than at run time.
> Redefine your datetime field as
>
> ...
> added datetime not null default text 'now',
> ...
>
> to force the type of the default value to be a string, which is then
> converted at run time as you would expect. Fixing this behavior would
> seem to be a good project for someone (maybe me :) for v6.6...
>
How about this?
added datetime default datetime(now()) not null,
Is this one OK?
Regards,
Chai
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] where did that date and time come from??
1999-04-20 14:40 ` Re: [INTERFACES] where did that date and time come from?? Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-04-23 09:13 ` Re: [INTERFACES] where did that date and time come from?? Chairudin Sentosa <chairudin@prima.net.id>
@ 1999-04-23 05:21 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Thomas Lockhart @ 1999-04-23 05:21 UTC (permalink / raw)
To: chairudin@prima.net.id; +Cc: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>; 'pgsql-admin@hub.org' <pgsql-admin@hub.org>; 'pgsql-interfaces@hub.org' <pgsql-interfaces@hub.org>
> How about this?
> added datetime default datetime(now()) not null,
Yes, this apparently works by converting from timestamp to datetime.
In general datetime has better dynamic range than does timestamp, but
for calculating 'now' both are equivalent in performance.
- Tom
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-04-23 09:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-20 00:07 ` Screech <screech@screech.org>
1999-04-20 14:40 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-04-23 09:13 ` Chairudin Sentosa <chairudin@prima.net.id>
1999-04-23 05:21 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
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