Yet Another Timestamp Question: Time Defaults

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, 21 Jan 2013, Adrian Klaver wrote:

Easy enough to test:

Thanks again, Adrian.

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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&gt;

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general