Thread: Yet Another Timestamp Question: Time Defaults
What is the behavior if a column data type is timestamptz but there is only the date portion available? There must be a default time; can that be defined? Rich
On 01/21/2013 07:26 AM, Rich Shepard wrote:
> What is the behavior if a column data type is timestamptz but there is
> only the date portion available? There must be a default time; can that be
> defined?
Easy enough to test:
test=# create table ts_test(ts_fld timestamp with time zone);
CREATE TABLE
test=# insert into ts_test VALUES ('2013-01-21');
INSERT 0 1
test=# SELECT * from ts_test ;
ts_fld
------------------------
2013-01-21 00:00:00-08
Not sure you can change the default supplied by Postgres, but you can on
your end:
test=# insert into ts_test VALUES ('2013-01-21'::date + interval '6' hour);
INSERT 0 1
test=# SELECT * from ts_test ;
ts_fld
------------------------
2013-01-21 00:00:00-08
2013-01-21 06:00:00-08
(2 rows)
>
> Rich
>
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
On Mon, 21 Jan 2013, Adrian Klaver wrote: > Easy enough to test: Thanks again, Adrian. Rich
Adrian Klaver <adrian.klaver@gmail.com> writes:
> On 01/21/2013 07:26 AM, Rich Shepard wrote:
>> What is the behavior if a column data type is timestamptz but there is
>> only the date portion available? There must be a default time; can that be
>> defined?
> Easy enough to test:
> test=# create table ts_test(ts_fld timestamp with time zone);
> CREATE TABLE
> test=# insert into ts_test VALUES ('2013-01-21');
> INSERT 0 1
> test=# SELECT * from ts_test ;
> ts_fld
> ------------------------
> 2013-01-21 00:00:00-08
Note that that default is local midnight according to your current
timezone setting (from which we may guess that Adrian lives on the US
west coast, or somewhere in that general longitude).
> Not sure you can change the default supplied by Postgres,
"SET timezone" ought to do it ...
regards, tom lane
On Mon, 21 Jan 2013, Tom Lane wrote: > Note that that default is local midnight according to your current > timezone setting (from which we may guess that Adrian lives on the US west > coast, or somewhere in that general longitude). Yep. About 3 hours north of me. >> Not sure you can change the default supplied by Postgres, > "SET timezone" ought to do it ... Thanks, Tom. Rich
On 01/21/2013 11:27 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@gmail.com> writes:
>> On 01/21/2013 07:26 AM, Rich Shepard wrote:
>>> What is the behavior if a column data type is timestamptz but there is
>>> only the date portion available? There must be a default time; can that be
>>> defined?
>
>> Easy enough to test:
>
>> test=# create table ts_test(ts_fld timestamp with time zone);
>> CREATE TABLE
>
>> test=# insert into ts_test VALUES ('2013-01-21');
>> INSERT 0 1
>
>> test=# SELECT * from ts_test ;
>> ts_fld
>> ------------------------
>> 2013-01-21 00:00:00-08
>
> Note that that default is local midnight according to your current
> timezone setting (from which we may guess that Adrian lives on the US
> west coast, or somewhere in that general longitude).
>
>> Not sure you can change the default supplied by Postgres,
>
> "SET timezone" ought to do it ...
I took Richs question to mean can you change the time portion supplied
by Postgres, so:
Instead of '2013-01-21' having the time portion set to local midnight it
could be set to a user supplied value say, 08:00:00. That is not
possible, correct. In the absence of a time portion a date string
supplied to timestamp will always get local midnight?
>
> regards, tom lane
>
--
Adrian Klaver
adrian.klaver@gmail.com
On 01/21/2013 11:27 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@gmail.com> writes:
>> On 01/21/2013 07:26 AM, Rich Shepard wrote:
>>> What is the behavior if a column data type is timestamptz but there is
>>> only the date portion available? There must be a default time; can that be
>>> defined?
>
>> Easy enough to test:
>
>> test=# create table ts_test(ts_fld timestamp with time zone);
>> CREATE TABLE
>
>> test=# insert into ts_test VALUES ('2013-01-21');
>> INSERT 0 1
>
>> test=# SELECT * from ts_test ;
>> ts_fld
>> ------------------------
>> 2013-01-21 00:00:00-08
>
> Note that that default is local midnight according to your current
> timezone setting (from which we may guess that Adrian lives on the US
> west coast, or somewhere in that general longitude).
>
>> Not sure you can change the default supplied by Postgres,
>
> "SET timezone" ought to do it ...
I took Richs question to mean can you change the time portion supplied
by Postgres, so:
Instead of '2013-01-21' having the time portion set to local midnight it
could be set to a user supplied value say, 08:00:00. That is not
possible, correct. In the absence of a time portion a date string
supplied to timestamp will always get local midnight?
>
> regards, tom lane
>
--
Adrian Klaver
adrian.klaver@gmail.com
On Monday, January 21, 2013 at 06:53, Adrian Klaver wrote:
....
>On 01/21/2013 11:27 AM, Tom Lane wrote:
>>Note that that default is local midnight according to your current
>>timezone setting (from which we may guess that Adrian lives on the US
>>west coast, or somewhere in that general longitude).
>>
>>>Not sure you can change the default supplied by Postgres,
>>
>>"SET timezone" ought to do it ...
>
>I took Richs question to mean can you change the time portion supplied by Postgres, so:
>
>Instead of '2013-01-21' having the time portion set to local midnight
>it could be set to a user supplied value say, 08:00:00. That is not
>possible, correct. In the absence of a time portion a date string
>supplied to timestamp will always get local midnight?
>
Thanks to all for the discussion of timestamps with/without
timezones I have been learning a lot from the side.
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. 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.
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.
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. :)
Regards
Gavan Schneider
On 01/21/2013 02:48 PM, Gavan Schneider wrote:
> On Monday, January 21, 2013 at 06:53, Adrian Klaver wrote:
> ....
>> On 01/21/2013 11:27 AM, Tom Lane wrote:
>>> Note that that default is local midnight according to your current
>>> timezone setting (from which we may guess that Adrian lives on the US
>>> west coast, or somewhere in that general longitude).
>>>
>>>> Not sure you can change the default supplied by Postgres,
>>>
>>> "SET timezone" ought to do it ...
>>
>> I took Richs question to mean can you change the time portion
>> supplied by Postgres, so:
>>
>> Instead of '2013-01-21' having the time portion set to local midnight
>> it could be set to a user supplied value say, 08:00:00. That is not
>> possible, correct. In the absence of a time portion a date string
>> supplied to timestamp will always get local midnight?
>>
> Thanks to all for the discussion of timestamps with/without timezones
> I have been learning a lot from the side.
>
> 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.)
> 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. The portions of the PostgreSQL manual dealing
with those data types bear careful and thoughtful reading and rereading
while you experiment at the same time in a psql terminal till it
"clicks." And while some time issues are universal, treatment varies
from program to program - especially regarding assumptions when the
input is ambiguous. I'm in the US Pacific time zone so without further
qualification, "2012-11-04 0130" could be 0130 PST or 0130 PDT.
The "date" program on my Linux desktop assumes daylight time:
date -d '2012-11-04 0130'
Sun Nov 4 01:30:00 PDT 2012
PostgreSQL assumes standard time:
select '2012-11-04 0130'::timestamptz;
timestamptz
------------------------
2012-11-04 01:30:00-08
Naturally this can lead to all sorts of "fun" when multiple technologies
are involved.
Meanwhile if I'm up at that hour and try to schedule a job for immediate
execution via "at now", the "at" program tells me it is "Cowardly
refusing to schedule a job in the past." So much for even internal
consistency.
>
>
> 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). 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.
BTW It's not at all "more accurate" - it is simply different definition.
> 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. :)
>
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.
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.
Cheers,
Steve
On Tuesday, January 22, 2013 at 09:48, I wrote: >(and I did report it as a bug back then) > Didn't pick this up on my pre-post re-read.... bug report was _NOT_ against PostgreSQL. It was some very early incarnations of OSX iCal, etc. which showed this behaviour. Apologies for the noise/confusion. Regards Gavan Schneider
On 01/21/2013 03:53 PM, Steve Crawford wrote:
> On 01/21/2013 02:48 PM, Gavan Schneider wrote:
>> On Monday, January 21, 2013 at 06:53, Adrian Klaver wrote:
>> ....
>>> On 01/21/2013 11:27 AM, Tom Lane wrote:
>>>> Note that that default is local midnight according to your current
>>>> timezone setting (from which we may guess that Adrian lives on the US
>>>> west coast, or somewhere in that general longitude).
>>>>
>>>>> Not sure you can change the default supplied by Postgres,
>>>>
>>>> "SET timezone" ought to do it ...
>>>
>>> I took Richs question to mean can you change the time portion
>>> supplied by Postgres, so:
>>>
>>> Instead of '2013-01-21' having the time portion set to local midnight
>>> it could be set to a user supplied value say, 08:00:00. That is not
>>> possible, correct. In the absence of a time portion a date string
>>> supplied to timestamp will always get local midnight?
>>>
>> Thanks to all for the discussion of timestamps with/without timezones
>> I have been learning a lot from the side.
>>
>> 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 must be missing something. I to am in PST:
test=# \d ts_test
Table "utility.ts_test"
Column | Type | Modifiers
--------+--------------------------+-----------
ts_fld | timestamp with time zone |
test=# INSERT INTO ts_test VALUES('2012-01-21');
test=# SELECT * from ts_test ;
ts_fld
------------------------
2012-01-21 00:00:00-08
test=# set timezone ='AKST9AKDT';
test=# SELECT ts_fld from ts_test;
ts_fld
------------------------
2012-01-20 23:00:00-09
>
> Cheers,
> Steve
>
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
On 01/21/2013 03:53 PM, Steve Crawford wrote: > On 01/21/2013 02:48 PM, Gavan Schneider wrote: >> On Monday, January 21, 2013 at 06:53, Adrian Klaver wrote: >> .... >>> On 01/21/2013 11:27 AM, Tom Lane wrote: >>>> Note that that default is local midnight according to your current >>>> timezone setting (from which we may guess that Adrian lives on the US >>>> west coast, or somewhere in that general longitude). >>>> >>>>> Not sure you can change the default supplied by Postgres, >>>> >>>> "SET timezone" ought to do it ... >>> >>> I took Richs question to mean can you change the time portion >>> supplied by Postgres, so: >>> >>> Instead of '2013-01-21' having the time portion set to local midnight >>> it could be set to a user supplied value say, 08:00:00. That is not >>> possible, correct. In the absence of a time portion a date string >>> supplied to timestamp will always get local midnight? >>> >> Thanks to all for the discussion of timestamps with/without timezones >> I have been learning a lot from the side. >> >> 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 see where my confusion lies. There are two proposals at work in the above: "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 " For the timestamp(alias for timestamp without time zone) case the date does not change. For timestamp with time zone it might. > Cheers, > Steve > > > -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver wrote: > I see where my confusion lies. There are two proposals at work in the above: > > "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 " > > For the timestamp(alias for timestamp without time zone) case the date > does not change. For timestamp with time zone it might. Well, the big problem here is in trying to use either version of timestamp when what you really want is a date. It will be much easier to get the right semantics if you use the date type for a date. -Kevin
On 01/21/2013 05:06 PM, Kevin Grittner wrote: > Adrian Klaver wrote: > >> I see where my confusion lies. There are two proposals at work in the above: >> >> "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" >> >> For the timestamp(alias for timestamp without time zone) case the date >> does not change. For timestamp with time zone it might. > > Well, the big problem here is in trying to use either version of > timestamp when what you really want is a date. It will be much > easier to get the right semantics if you use the date type for a > date. Agreed. If I was following Gavan correctly, he wanted to have a single timestamp field to store calender dates and datetimes. In other words to cover both date only situations like birthdays and datetime situations like an appointment. > > -Kevin > > -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver wrote: > If I was following Gavan correctly, he wanted to have a single > timestamp field to store calender dates and datetimes. In other > words to cover both date only situations like birthdays and > datetime situations like an appointment. If that is actually true, it sounds like some reading on the benefits of normalizing to 3rd normal form is in order. What you describe is a violation of first normal form. Now, I recognize that most databases of any complexity need to denormalize to one degree or another for performance reasons; but I don't see the benefit of this particular type of denormalization. -Kevin
On Monday, January 21, 2013 at 12:06, Kevin Grittner wrote:
>Adrian Klaver wrote: [Actually Gavan Schneider wrote this, don't blame Adrian :]
>
>>I see where my confusion lies. There are two proposals at work in the above:
>>
>>"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 "
>>
>>For the timestamp(alias for timestamp without time zone) case
>>the date does not change. For timestamp with time zone it might.
>
>Well, the big problem here is in trying to use either version of
>timestamp when what you really want is a date. It will be much
>easier to get the right semantics if you use the date type for a
>date.
>
This is the cleanest solution.
And I did not want to imply the following...
Adrian Klaver wrote:
>
>If I was following Gavan correctly, he wanted to have a single
>timestamp field to store calender dates and datetimes. In other
>words to cover both date only situations like birthdays and
>datetime situations like an appointment.
My discussion really only applies to some notion of the best
(or, more exactly, the least wrong) time to attribute to a date
when conversion to timestamp happens for whatever reason. And,
as indicated in my original post, I have been stung when dates
got (badly) mixed into a datetime timezone aware context.
The points raised by Adrain have prompted some more research on
my part and I am intrigued to learn that on one day of the year
in many countries (e.g., Brazil) where daylight conversion
happens over midnight the local-time version of midnight as
start of day does not exist. Basically the last day of
unadjusted time ends at midnight and rolls directly into
01:00:00 the next day (i.e., time 00:00:00 never happens on this
one day). So the current date-> date+time system must already
have some added complexity/overhead to check for this rare
special case. (If not, there's a bug needs fixing!)
Basically midnight is not safe as a target entity once timezones
and daylight saving get involved. Midday, on the other hand, is
a very solid proposition, no checks required, 12:00:00 will
happen in all time zones on every day of the year! Basically
nobody messes with their clocks in the middle of the day.
So restating:
'2013-10-20'::timestamp ==> 2013-10-20 12:00:00 can never
be wrong; but,
'2013-10-20'::timestamp ==> 2013-10-20 00:00:00 is wrong in
some places.
Regards
Gavan Schneider
On 01/21/2013 07:40 PM, Gavan Schneider wrote: > On Monday, January 21, 2013 at 12:06, Kevin Grittner wrote: >> >> Well, the big problem here is in trying to use either version of >> timestamp when what you really want is a date. It will be much >> easier to get the right semantics if you use the date type for a >> date. >> > This is the cleanest solution. > > And I did not want to imply the following... Well, another fine assumption shot down:) > > Adrian Klaver wrote: >> >> If I was following Gavan correctly, he wanted to have a single >> timestamp field to store calender dates and datetimes. In other >> words to cover both date only situations like birthdays and >> datetime situations like an appointment. > > The points raised by Adrain have prompted some more research on my part > and I am intrigued to learn that on one day of the year in many > countries (e.g., Brazil) where daylight conversion happens over midnight > the local-time version of midnight as start of day does not exist. > Basically the last day of unadjusted time ends at midnight and rolls > directly into 01:00:00 the next day (i.e., time 00:00:00 never happens > on this one day). So the current date-> date+time system must already > have some added complexity/overhead to check for this rare special case. > (If not, there's a bug needs fixing!) 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. > > Regards > Gavan Schneider > > > -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> writes:
> 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.
Yeah, that's for sure. Anyway, I think we are exceedingly unlikely to
adopt Gavan's suggestion. It would break a huge amount of existing
application code, and I think it is also arguably contrary to the SQL
standard. The standard doesn't specify (at least, not that I've found)
the external representation of datatype values; but it does specify what
they're supposed to look like within literal constants in SQL commands.
At least in SQL92 and SQL99 (too lazy to look at other versions right
now), a timestamp literal that omits the time-of-day part is flat out
illegal:
<unquoted date string> ::= <date value>
<unquoted time string> ::=
<time value> [ <time zone interval> ]
<unquoted timestamp string> ::=
<unquoted date string> <space> <unquoted time string>
Note the lack of square brackets there. The only way that you can
really reconcile the spec with using just a <date value> in timestamp
input is to suppose that the input is meant as a date and then we apply
an implicit cast to timestamp. However, the spec definitely has an
opinion on the meaning of such a cast. In 6.22 <cast specification>,
SD and TD are the source and target datatypes for a cast, SV and TV are
the source and target values:
17) If TD is the datetime data type TIMESTAMP WITHOUT TIME ZONE,
then let TSP be the <timestamp precision> of TD.
b) If SD is a date, then the <primary datetime field>s hour,
minute, and second of TV are set to 0 (zero) and the <primary
datetime field>s year, month, and day of TV are set to their
respective values in SV.
18) If TD is the datetime data type TIMESTAMP WITH TIME ZONE, then
let TSP be the <time precision> of TD.
b) If SD is a date, then TV is:
CAST (CAST (SV AS TIMESTAMP(TSP) WITHOUT TIME ZONE)
AS TIMESTAMP(TSP) WITH TIME ZONE)
(the behavior of that is defined as a timezone rotation)
So it seems to me that the spec is pretty clearly on the side of filling
in zeroes, ie local midnight.
Now, you might say that there's an easy way around both the application
breakage and the spec-compliance objections: let's just define a new GUC
parameter that selects the behavior, with a backwards-compatible default
setting. And ten years ago, I'd have probably said "hey, that's a great
idea". But one of the things I've learned as the project goes along is
that GUCs that affect application-visible semantics are dangerous
things. Robust application code has to be made to cope with any
possible setting of such a GUC, which makes them not nearly such a cheap
fix as they seem initially. Especially not if the behavioral change is
silent, with no possibility of detecting or reporting an error if the
application is not expecting the new behavior.
regards, tom lane
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
On Monday, January 21, 2013 at 15:33, Tom Lane wrote:
>I think it is also arguably contrary to the SQL standard...
>
>17) If TD is the datetime data type TIMESTAMP WITHOUT TIME ZONE,
>then let TSP be the <timestamp precision> of TD.
>
>b) If SD is a date, then the <primary datetime field>s hour,
>minute, and second of TV are set to 0 (zero) and the <primary
>datetime field>s year, month, and day of TV are set to their
>respective values in SV.
>
That has to be the trump card.
>... let's just define a new GUC parameter that selects the behavior,
>with a backwards-compatible default setting. ... Robust application
>code has to be made to cope with any possible setting of such a GUC,
>which makes them not nearly such a cheap fix as they seem
>initially. ...
>
and, why go to significant trouble to implement standards
non-compliance when there is no legacy code to support?
I could always wish the SQL committee had thought along my lines
all those years ago, and then again, I could just do something
useful. :)
On Monday, January 21, 2013 at 11:38, Adrian Klaver wrote:
>I must be missing something. I to am in PST:
>
>test=# \d ts_test
>Table "utility.ts_test"
>Column | Type | Modifiers
>--------+--------------------------+-----------
>ts_fld | timestamp with time zone |
>
>
>test=# INSERT INTO ts_test VALUES('2012-01-21');
>
>test=# SELECT * from ts_test ;
>ts_fld
>------------------------
>2012-01-21 00:00:00-08
>
>test=# set timezone ='AKST9AKDT';
>
>test=# SELECT ts_fld from ts_test;
>ts_fld
>------------------------
>2012-01-20 23:00:00-09
>
The only thing missed is we are saying much same thing. There is
no problem with the conversion. It is, as we see from Tom, fully
SQL compliant. The only "problem" is when you are more
interested in the date itself and not the point in time. This is
just one of several scenarios where the date might get changed
in ways that could be difficult to trace... caveat coder.
Thanks again everyone for a lot more clarity in my thinking
about dates times and timezones.
Regards
Gavan Schneider
Monday, January 21, 2013, 8:56:38 PM, you wrote: >>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. I only wish. I work with a transactional system from the 70s on a daily basis that decided to store something like a "work date" and "work time". The date changes whenever they decide to dateroll the system. Until then the time field continues to grow, so you see times like 25:00 and 26:00 all the time. Exceptions abound. -- Nathan
On 01/21/2013 08:56 PM, Gavan Schneider wrote: > 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. We can call it all sorts of things but it is, in fact, the same point in time. What you have done is omitted a critical piece of information necessary for a "fully qualified" point-in-time - the time zone. Now if I tell my wife I'll be home by 6 she says, "OK, see you then," not "do you mean AM or PM? Er, Pacific time? Today?" In other words she makes reasonable assumptions about what point-in-time I am conveying. (Aside...It reminds me of the joke about the father admonishing his daughter's date to have her back by ten-fifteen to which he responds, "Mid-October? Cool!") A date alone can be interpreted as any of a number of points-in-time covering a roughly two-day range: select '2013-01-22 23:59 posix/Pacific/Midway'::timestamptz - '2013-01-22 00:00 Pacific/Kiritimati'::timestamptz; 1 day 01:00:00 So in order to calculate a single point-in-time, PostgreSQL, like my wife, has to make certain assumptions regarding the missing information (and fortunately PostgreSQL follows the SQL spec in this regard). The assumptions it makes are: 1) Interpret the date in local time not the date somewhere else in the world. 2) Interpret the missing time portion as 00:00:00. You now have a point-in-time, not a date. You can display that point-in-time in whatever timezone you wish and some will have the same date as your local date while others will not. Assuming that the time is 12:00:00 rather than 00:00:00 does not change that fact: --localtime is US Pacific select '2013-01-22 12:00'::timestamptz at time zone 'Pacific/Kiritimati'; 2013-01-23 10:00:00 Cheers, Steve
On 01/21/2013 07:40 PM, Gavan Schneider wrote:
> ...
> The points raised by Adrain have prompted some more research on my
> part and I am intrigued to learn that on one day of the year in many
> countries (e.g., Brazil) where daylight conversion happens over
> midnight the local-time version of midnight as start of day does not
> exist. Basically the last day of unadjusted time ends at midnight and
> rolls directly into 01:00:00 the next day (i.e., time 00:00:00 never
> happens on this one day). So the current date-> date+time system must
> already have some added complexity/overhead to check for this rare
> special case. (If not, there's a bug needs fixing!)
>
> Basically midnight is not safe as a target entity once timezones and
> daylight saving get involved. Midday, on the other hand, is a very
> solid proposition, no checks required, 12:00:00 will happen in all
> time zones on every day of the year! Basically nobody messes with
> their clocks in the middle of the day.
>
> So restating:
> '2013-10-20'::timestamp ==> 2013-10-20 12:00:00 can never be
> wrong; but,
> '2013-10-20'::timestamp ==> 2013-10-20 00:00:00 is wrong in some
> places.
"Wrong" times occur in every time zone that changes offsets at various
points of the year. Here in California, 02:00:00-02:59:59 March 10, 2013
are "wrong" but PostgreSQL uses a reasonable interpretation to yield a
point-in-time:
select '2013-03-10 0230'::timestamptz;
timestamptz
------------------------
2013-03-10 03:30:00-07
And it does the exact same thing in Brazil:
set timezone to 'Brazil/West';
select '1993-10-17 00:00'::timestamptz;
timestamptz
------------------------
1993-10-17 01:00:00-03
select '1993-10-17'::timestamptz;
timestamptz
------------------------
1993-10-17 01:00:00-03
Note, too, that in both zones when the input is interpreted in the local
zone and displayed in the local zone the date-portion of the
point-in-time is the same as the input date. (While I suppose some
politician somewhere could decide that "fall-back" could cross date
boundaries, I am unaware of any place that has ever done something so
pathological as to have the same date occur in two non-contiguous pieces
once every year.)
Cheers,
Steve
On 23/01/13 06:30, Gavan Schneider wrote: > On 01/21/2013 07:40 PM, Gavan Schneider wrote: [...] > (While I suppose some politician somewhere could decide that > "fall-back" could cross date boundaries, I am unaware of any place > that has ever done something so pathological as to have the same date > occur in two non-contiguous pieces once every year.) [...] Don't tempt the gods!!! :-) Cheers, Gavin
On 01/22/2013 09:37 AM, Gavin Flower wrote: > On 23/01/13 06:30, Gavan Schneider wrote: >> On 01/21/2013 07:40 PM, Steve Crawford wrote: > [...] >> (While I suppose some politician somewhere could decide that >> "fall-back" could cross date boundaries, I am unaware of any place >> that has ever done something so pathological as to have the same date >> occur in two non-contiguous pieces once every year.) > [...] > > Don't tempt the gods!!! :-) > Sorry. :) Cheers, Steve
On Monday, January 21, 2013 at 18:11, bgd39h5xxt@sneakemail.com (Nathan Clayton nathanclayton-at-gmail.com |pg-gts/Basic|) wrote: >I only wish. I work with a transactional system from the 70s on >a daily basis that decided to store something like a "work date" and >"work time". The date changes whenever they decide to dateroll the >system. Until then the time field continues to grow, so you see times >like 25:00 and 26:00 all the time. > SELECT execute(relevant_dba) FROM the_70s WITH tardis WHERE working_tardis = true; >Exceptions abound. > At least that can't be blamed on a government, and, we can only hope ISO-8601 will prevent more examples being created. You sound as though you really need, and/or already have, a dedicated datatype... if only to stop 'the system' from 'fixing' such weirdness. Regards Gavan Schneider
On 2013-01-21, Rich Shepard <rshepard@appl-ecosys.com> wrote: > What is the behavior if a column data type is timestamptz but there is > only the date portion available? There must be a default time; can that be > defined? No, if you don't specify the time 00:00 (midnight) is used. if you don't specify a timezone it's local midnight, so viewed from a neighbouring timezone it could be 1AM or 11pm the previous day. -- ⚂⚃ 100% natural
On 2013-01-21, Gavan Schneider <pg-gts@snkmail.com> wrote: > On Monday, January 21, 2013 at 06:53, Adrian Klaver wrote: > .... > timezones I have been learning a lot from the side. > > 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. that does not work, anywhere in the world, any time of day,it's always a different day somewhere (American Samoa vs Rarotonga be an extreme example, but one or the other will qualify if nowhere else does) If you want a date field use a date field, you can't reliably fake it using timestamptz -- ⚂⚃ 100% natural
On 2013-01-21, Steve Crawford <scrawford@pinpointresearch.com> wrote: > > Date/time is not trivial. The portions of the PostgreSQL manual dealing > with those data types bear careful and thoughtful reading and rereading > while you experiment at the same time in a psql terminal till it > "clicks." And while some time issues are universal, treatment varies > from program to program - especially regarding assumptions when the > input is ambiguous. I'm in the US Pacific time zone so without further > qualification, "2012-11-04 0130" could be 0130 PST or 0130 PDT. noveber suggests PST failry stongly. > The "date" program on my Linux desktop assumes daylight time: > date -d '2012-11-04 0130' > Sun Nov 4 01:30:00 PDT 2012 november is the DST changeover? > PostgreSQL assumes standard time: > select '2012-11-04 0130'::timestamptz; > timestamptz > ------------------------ > 2012-11-04 01:30:00-08 > > Naturally this can lead to all sorts of "fun" when multiple technologies > are involved. > > Meanwhile if I'm up at that hour and try to schedule a job for immediate > execution via "at now", the "at" program tells me it is "Cowardly > refusing to schedule a job in the past." So much for even internal > consistency. theres an hour in the night that I've learned to never schedlule cron jobs that must run atleast once or at most once. > 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. -- ⚂⚃ 100% natural