Undocumented datetime functions

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

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/

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