On Monday, January 21, 2013 at 10:53, Steve Crawford wrote:
On 01/21/2013 02:48 PM, Gavan Schneider wrote:
....
Taking another tangent I would much prefer the default time to
be 12:00:00 for the conversion of a date to timestamp(+/-timezone).
Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00
The benefit of the midday point is that the actual date will
not change when going through the timezone conversion.
Just like it doesn't change now? (I just checked against all of the
more than 1,100 zones in PG without seeing a problem.)
I find this result strange to say the least... our conversation
is straddling Monday(you)/Tuesday(me). We shared the time point
2013-01-22 01:30 UTC, but called it different things, viz.,
2013-01-22 12:30 and 2013-01-21 17:30.
And any definition based on midnight(UTC) will cast to either
side of the date line depending on the local timezone. This "is
not a problem" per se. It just brings me back to my point that
sometimes the date is more important than the notion of a point
in time. Hence:
This has implications for time-of-day insensitive data such as
birthdays and other calendar values. I am still resolving "off
by one day" errors that crept into many entries in my calendar
and contacts from several years ago when data was added while
travelling across multiple time zones (and I did report it as
a bug back then). With this lesson learnt the workaround for
me in my own applications since has been to store such dates
as point-in-time for midday while keeping track of the
input/output so it only gets used as a date... sometimes
tedious, and a last resort. Mostly I have been actively
avoiding anything with the taint of timezone due to this bad
experience. It's time to reconsider, I guess, since this can
cause other forms of silly behaviour.
Date/time is not trivial. ...
Total agreement here. And, as I said, I am going to school on
this with a lot more insight after your's and other's input.
...
Meanwhile if I'm up at that hour and try to schedule a job ...
or possibly one of your machines is on the other side of the
planet and running on tomorrow's time
Aesthetically (and/or mathematically) the midday point is more
accurate. It is the middle of the relevant interval (i.e., 24
hours) implied by a date. Midnight is the extreme edge of any
date (i.e., not what you would consider as mid-target).
"Midnight" also has confusing English semantics since it can
belong to either of its adjacent days.
Except for days that are 23-hours long, or 25, or other (it's a big
world with all sorts of timezone rules).
The day's length may change but I don't believe there is
anywhere that allows for the local time of day to equal or be
greater than 24:00:00 without rolling over to the next day.
How would that fit with ISO-8601?
<http://en.wikipedia.org/wiki/ISO_8601#Times>
It's also very useful for common queries (select ... from somelog
where logtime > current_date) and provides a known starting-point from
which you can easily calculate the offsets you desire.
Agree, but aren't we better writing something like:
SELECT ... FROM somelog WHERE logtime::date >= CURRENT_DATE;
and not relying on an implementation detail for correct behaviour.
Timestamps can always be busted back to lesser precision, i.e.,
date only, but adding time information to a date is
extrapolation. IMNSHO this sort of thing should be avoided.
I don't know if the current behaviour will be deemed to be too
rusted in place for change, or if this proposal has too many
adverse consequences, but hope springs eternal. :)
Obviously there is no discussion if current PostgreSQL behaviour
is SQL standards compliant. I don't think anyone should ask that
existing standards compliance be undone.
It would sure break a lot of my queries. And for the many people who
want/expect the date to cast to date at 00:00:00 local time it would
lead to a load of pitfalls such as naively subtracting 12-hours or
requiring the programmer to add complexity to determine how many hours
to subtract based on local time zone and current date.
This is assuming that someone would need to "correct" the hour
when there was never any time of day information originally
present. The naivety here is in attempting to correct something
that is arbitrary. This is already a problem with the current
system when attempting to "correct" times in all timezones,
i.e., how many hours to add for a least wrong estimate of the time?
But you are, of course, free to use the capability that PostgreSQL
gives you to define pretty much any data-type you want along with your
desired casting rules if you so desire. Just don't expect the built-in
definitions to change.
Thinking only, but it's way too early on my learning curve to
venture there since such a data-type still has to play correctly
with the rest of the system. And once I better know the system I
may well have learnt to mitigate correctly in the relevant
places. Mostly I avoid mixing timestamps with dates but figure I
can't hide forever.
On Monday, January 21, 2013 at 14:53, Adrian Klaver wrote:
If I have learned anything about dealing with dates and times, is that
it is a set of exceptions bound together by a few rules. Every time
you think you have the little rascals cornered, one gets away.
One more level of nesting and we have a quote of Churchillian
scope. :)
Regards
Gavan Schneider
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
