Thread: Undocumented datetime functions
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
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/
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
> 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
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
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/
> > 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
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