Re: Yet Another Timestamp Question: Time Defaults - Mailing list pgsql-general

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