pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedUndocumented datetime functions
8+ messages / 3 participants
[nested] [flat]
* Undocumented datetime functions
@ 2001-02-15 03:58 Tom Lane <tgl@sss.pgh.pa.us>
2001-02-16 18:10 ` Re: Undocumented datetime functions Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2001-02-15 03:58 UTC (permalink / raw)
To: pgsql-docs
The following date/time related functions are not documented in the
obvious place for them, namely
http://www.postgresql.org/devel-corner/docs/postgres/functions-datetime.htm
now()
CURRENT_DATE
CURRENT_TIME
CURRENT_TIME(n)
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP(n)
EXTRACT(part FROM datetime) --- ANSIfied version of date_part
BTW, it seems that date_part accepts rather more options than are
documented on this page, as well.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Undocumented datetime functions
2001-02-15 03:58 Undocumented datetime functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-02-16 18:10 ` Peter Eisentraut <peter_e@gmx.net>
2001-02-16 18:09 ` Re: Undocumented datetime functions Tom Lane <tgl@sss.pgh.pa.us>
2001-02-16 19:21 ` Re: Undocumented datetime functions Thomas Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 2 replies; 8+ messages in thread
From: Peter Eisentraut @ 2001-02-16 18:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs; Thomas Lockhart <lockhart@fourpalms.org>
Tom Lane writes:
> The following date/time related functions are not documented in the
> obvious place for them, namely
> http://www.postgresql.org/devel-corner/docs/postgres/functions-datetime.htm
>
> now()
> CURRENT_DATE
> CURRENT_TIME
> CURRENT_TIME(n)
> CURRENT_TIMESTAMP
> CURRENT_TIMESTAMP(n)
> EXTRACT(part FROM datetime) --- ANSIfied version of date_part
Coming up...
> BTW, it seems that date_part accepts rather more options than are
> documented on this page, as well.
I'm confused about tz_hour and tz_minute. Are the regular 'hour' and
'minute' time zone asensitive?
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Undocumented datetime functions
2001-02-15 03:58 Undocumented datetime functions Tom Lane <tgl@sss.pgh.pa.us>
2001-02-16 18:10 ` Re: Undocumented datetime functions Peter Eisentraut <peter_e@gmx.net>
@ 2001-02-16 18:09 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2001-02-16 18:09 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-docs@postgresql.org, Thomas Lockhart <lockhart@fourpalms.org>
Peter Eisentraut <peter_e@gmx.net> writes:
> I'm confused about tz_hour and tz_minute.
Lockhart would be the authority here, but I think that tz_hour/tz_minute
tell you your timezone in the form of hours & minutes west of GMT.
'timezone' gives the same info in a pure total-minutes-west style.
(There are places that run with standard time a nonintegral number of
hours off GMT, oddly enough, so you do need the minutes part.)
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Undocumented datetime functions
2001-02-15 03:58 Undocumented datetime functions Tom Lane <tgl@sss.pgh.pa.us>
2001-02-16 18:10 ` Re: Undocumented datetime functions Peter Eisentraut <peter_e@gmx.net>
@ 2001-02-16 19:21 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
2001-02-17 16:34 ` Re: Undocumented datetime functions Peter Eisentraut <peter_e@gmx.net>
1 sibling, 1 reply; 8+ messages in thread
From: Thomas Lockhart @ 2001-02-16 19:21 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs@postgresql.org, Thomas Lockhart <lockhart@fourpalms.org>
> I'm confused about tz_hour and tz_minute. Are the regular 'hour' and
> 'minute' time zone asensitive?
Regular hour and minute arguments return values interpreted in the
currently valid time zone, except for types which have no affiliated
time zone (e.g. "time").
tz_hour and tz_minute return the hour and minutes fields of the current
time zone, or stored time zone for the timetz type.
- Thomas
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Undocumented datetime functions
2001-02-15 03:58 Undocumented datetime functions Tom Lane <tgl@sss.pgh.pa.us>
2001-02-16 18:10 ` Re: Undocumented datetime functions Peter Eisentraut <peter_e@gmx.net>
2001-02-16 19:21 ` Re: Undocumented datetime functions Thomas Lockhart <lockhart@alumni.caltech.edu>
@ 2001-02-17 16:34 ` Peter Eisentraut <peter_e@gmx.net>
2001-02-17 16:28 ` Re: Undocumented datetime functions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2001-02-17 16:34 UTC (permalink / raw)
To: thomas@pgsql.com; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs; Thomas Lockhart <lockhart@fourpalms.org>
Thomas Lockhart writes:
> > I'm confused about tz_hour and tz_minute. Are the regular 'hour' and
> > 'minute' time zone asensitive?
>
> Regular hour and minute arguments return values interpreted in the
> currently valid time zone, except for types which have no affiliated
> time zone (e.g. "time").
>
> tz_hour and tz_minute return the hour and minutes fields of the current
> time zone, or stored time zone for the timetz type.
Hmm, I'm confused. The timestamp type doesn't actually have a time zone
stored, does it? Why did you want it to be dumped as "timestamp with time
zone" then?
Also, it's not possible to cast "time with time zone" to "timestamp", so
it's not possible to make real use of extract(timezone_... ).
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Undocumented datetime functions
2001-02-15 03:58 Undocumented datetime functions Tom Lane <tgl@sss.pgh.pa.us>
2001-02-16 18:10 ` Re: Undocumented datetime functions Peter Eisentraut <peter_e@gmx.net>
2001-02-16 19:21 ` Re: Undocumented datetime functions Thomas Lockhart <lockhart@alumni.caltech.edu>
2001-02-17 16:34 ` Re: Undocumented datetime functions Peter Eisentraut <peter_e@gmx.net>
@ 2001-02-17 16:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-17 23:29 ` Re: Undocumented datetime functions Thomas Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2001-02-17 16:28 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-docs@postgresql.org, Thomas Lockhart <lockhart@fourpalms.org>
Peter Eisentraut <peter_e@gmx.net> writes:
> Hmm, I'm confused. The timestamp type doesn't actually have a time zone
> stored, does it? Why did you want it to be dumped as "timestamp with time
> zone" then?
Andreas pointed that out awhile ago. I'm inclined to agree: equating
timestamp to timestamp with time zone is dead wrong, and we should
revert that pg_dump change.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Undocumented datetime functions
2001-02-15 03:58 Undocumented datetime functions Tom Lane <tgl@sss.pgh.pa.us>
2001-02-16 18:10 ` Re: Undocumented datetime functions Peter Eisentraut <peter_e@gmx.net>
2001-02-16 19:21 ` Re: Undocumented datetime functions Thomas Lockhart <lockhart@alumni.caltech.edu>
2001-02-17 16:34 ` Re: Undocumented datetime functions Peter Eisentraut <peter_e@gmx.net>
2001-02-17 16:28 ` Re: Undocumented datetime functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-02-17 23:29 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
2001-02-17 23:47 ` Re: Undocumented datetime functions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Thomas Lockhart @ 2001-02-17 23:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-docs@postgresql.org, Thomas Lockhart <lockhart@fourpalms.org>
> > Hmm, I'm confused. The timestamp type doesn't actually have a time zone
> > stored, does it? Why did you want it to be dumped as "timestamp with time
> > zone" then?
Because it has "time zone aware behavior". And the input carries time
zone information, either implicitly or explicitly. And the output
carries time zone information. But...
> Andreas pointed that out awhile ago. I'm inclined to agree: equating
> timestamp to timestamp with time zone is dead wrong, and we should
> revert that pg_dump change.
This issue is not as black and white as you seem to think. As you
probably know, the SQL9x definitions for date/time types are
fundamentally flawed, with no possibility for awareness of DST, local
time, etc etc. Cf Date and Darwen for a discussion of other aspects of
the problems. I don't really care whether what we currently have is
"timestamp" or "timestamp with time zone", but if, for example, I/we
implement an SQL9x-conforming "timestamp with time zone" it will not get
used. So where do we want our current data type to fit in, and how do we
want to "fill in the edges" of our feature set? An enlightened
discussion would be helpful here, though since we are not in a position
to discuss fundamental changes at the moment perhaps limiting it to
"which side of the fence?" for the existing timestamp implementation
would be sufficient.
- Thomas
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Undocumented datetime functions
2001-02-15 03:58 Undocumented datetime functions Tom Lane <tgl@sss.pgh.pa.us>
2001-02-16 18:10 ` Re: Undocumented datetime functions Peter Eisentraut <peter_e@gmx.net>
2001-02-16 19:21 ` Re: Undocumented datetime functions Thomas Lockhart <lockhart@alumni.caltech.edu>
2001-02-17 16:34 ` Re: Undocumented datetime functions Peter Eisentraut <peter_e@gmx.net>
2001-02-17 16:28 ` Re: Undocumented datetime functions Tom Lane <tgl@sss.pgh.pa.us>
2001-02-17 23:29 ` Re: Undocumented datetime functions Thomas Lockhart <lockhart@alumni.caltech.edu>
@ 2001-02-17 23:47 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2001-02-17 23:47 UTC (permalink / raw)
To: lockhart@fourpalms.org; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-docs
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> ... I don't really care whether what we currently have is
> "timestamp" or "timestamp with time zone", but if, for example, I/we
> implement an SQL9x-conforming "timestamp with time zone" it will not get
> used.
Okay, if we believe those two facts, then calling our existing timestamp
type "timestamp with time zone" does not make us more standards
conformant, it only makes us more verbose. I'd prefer to use the
shorter name for the datatype we believe is actually useful ...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2001-02-17 23:47 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-15 03:58 Undocumented datetime functions Tom Lane <tgl@sss.pgh.pa.us>
2001-02-16 18:10 ` Peter Eisentraut <peter_e@gmx.net>
2001-02-16 18:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-16 19:21 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
2001-02-17 16:34 ` Peter Eisentraut <peter_e@gmx.net>
2001-02-17 16:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-17 23:29 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
2001-02-17 23:47 ` Tom Lane <tgl@sss.pgh.pa.us>
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