Thread: Timestamp & Interval - Part 1
Tom, Oliver, I haven't finished writing up my ideas for INTERVAL. However, here's something to get started: PROPOSAL FOR ADJUSTMENTS OF POSTGRESQL TIMESTAMP AND INTERVAL HANDLING Draft 0.1 - Part 1 Timestamp ------------------------------ Proposal #1: TIMESTAMP WITHOUT TIME ZONE as default Description: Currently, the data type invoked when users select TIMESTAMP is TIMESTAMP WITH TIME ZONE. We should change this so that TIMESTAMP defaults to TIMESTAMP WITHOUT TIME ZONE unless WITH TIME ZONE is specificied. Reason: Handling time zones is tricky and non-intuitive for the beginning user. TIMESTAMP WITH TIME ZONE should be reserved for DBAs who know what they're doing. Proposal #2: We need more time zones. Description: We need to add, or be able to add, many new time zones to Postgresql. Ideal would be some kind of "create time zone" statement. Reason: Current included time zones do not cover all real-world time zones, and the situation is likely to get worse as various governments play with their calendars. For example, there is no current time zone which would be appropriate for the state of Arizona, i.e. "Central Standard Time without Daylight Savings Time". Further: A CREATE TIME ZONE statement would have the following syntax: CREATE TIME ZONE GMT_adjustment, abbreviation, uses_DST, DST_starts (optional), DST_ends (optional) This would allow, to some degree, DBA creation of time zones to take into account local laws and wierdnesses. -- -Josh Berkus
> Proposal #1: TIMESTAMP WITHOUT TIME ZONE as default
Hmm. Already done for 7.3 :)
7.2 introduced that data type, and 7.1 did not have it, so we had one
release cycle to allow dump/restore to do the right thing.
> Proposal #2: We need more time zones.
The other complaint is that we have too many time zones. Certainly it is
not ideal (but it may be optimal from an execution time standpoint) that
these time zones are hardcoded into lookup tables; moving these into
external files will be *much* slower, moving these into database tables
will be somewhat slower. But asking us to deal with Arizona may be a bit
too much; those guys do things just to be different ;)
btw, on my Linux box the time zone rule is 'US/Arizona', as in
lockhart=# SET TIME ZONE 'US/Arizona';
My Linux box thinks that for Arizona time input would always be in
'MST', which is recognized by the PostgreSQL date/time parser so things
are handled consistantly (at least until I upgrade glibc :((
Let's see how the glibc breakage discussion pans out. I haven't worried
about pre-1903 dates and times because time zones were not as
standardized then as they are today. But if we end up rolling our own
then we might consider pulling more of this into the backend and getting
rid of our y2038 problems at the same time :))
- Thomas
Why not fix it completely with this stuff: ftp://elsie.nci.nih.gov/pub/ Just an idea.
> Why not fix it completely with this stuff:
> ftp://elsie.nci.nih.gov/pub/
> Just an idea.
Ah, the real zic implementation. afaik this is public domain or BSD or
at least compatible with our BSD license wrt distribution.
Great idea. We may end up doing this! Though I hate for the project to
pick up the task of maintaining sync with that distro.
We already have a NO_MKTIME_BEFORE_1970 #define'd for AIX and IRIX
(always paragons of standard behavior :/ Funny enough it doesn't
actually guard the mktime() code, since I think that there is a good
signature from the exit from mktime() on those systems (independent of
the return value) to detect that there is a problem. glibc is sinking to
new depths in lack of support for this feature by brute force exiting
early on.
It looks like we might (easily?) get good behavior beyond y2038, since
we might be able to redefine time_t within our code. At the moment zic
looks for it from sys/types.h, but maybe we could isolate it and force
it to be a 64-bit number on systems which support it. Hmm, need to find
how to translate current system time to that representation...
- Thomas