Timestamp with and without timezone conversion confusion.

Tim Uckun wrote:

I have the following query.

[...]

SELECT
interval_start,
(interval_start AT TIME ZONE 'Africa/Monrovia')::timestamp with time zone as
interval_start_in_africa,
min_datetime,
min_datetime AT TIME ZONE 'Europe/Berlin' as min_datetime_in_berlin,
max_datetime,
max_datetime AT TIME ZONE 'America/New_York' as max_datetime_in_new_york

FROM grouped_data gd

When I run this query in pgadmin I get the following results

"interval_start","interval_start_in_africa","min_datetime","min_datetime_in_berlin","max_datetime","ma
x_datetime_in_new_york"
"2013-10-04 15:35:00+13","2013-10-04 02:35:00+13","2013-10-04 15:35:00+13","2013-10-04
04:35:00","2013-10-04 15:39:59+13","2013-10-03 22:39:59"
"2013-10-04 15:25:00+13","2013-10-04 02:25:00+13","2013-10-04 15:28:11+13","2013-10-04
04:28:11","2013-10-04 15:29:59+13","2013-10-03 22:29:59"
"2013-10-04 15:40:00+13","2013-10-04 02:40:00+13","2013-10-04 15:40:00+13","2013-10-04
04:40:00","2013-10-04 15:44:39+13","2013-10-03 22:44:39"
"2013-10-04 15:30:00+13","2013-10-04 02:30:00+13","2013-10-04 15:30:00+13","2013-10-04
04:30:00","2013-10-04 15:34:59+13","2013-10-03 22:34:59"

Notice that all the offsets are set to +13 which is my laptop's offset. Why don't they show the offset
of Africa or Berlin or whatever?

The configuration parameter "TimeZone" determines how "timestamp with
time zone" is interpreted and converted to a string.

The reason for that is that in PostgreSQL there is no time zone
information stored along with a "timestamp with time zone",
it is stored in UTC.

Also note then unless I explictly cast the data as timestamp with
time zone all the offsets go away and it's reported as timestamp without time zone.

That is because AT TIME ZONE returns a "timestamp without time zone"
in this case, see the documentation.

Yours,
Laurenz Albe

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

The reason for that is that in PostgreSQL there is no time zone

information stored along with a "timestamp with time zone",
it is stored in UTC.

That seems unintuitive. What is the difference between timestamp without
time zone and timestamp with time zone? I was expecting to have the time
zone stored in the field. For example one row might be in UTC but the
other row might be in my local time.

Maybe the question I need to ask is "how can I store the time zone along
with the timestamp"

That is because AT TIME ZONE returns a "timestamp without time zone"

Also seems counterintutive but I guess I can aways convert it. I am just
not getting the right offset when I convert. That's what's puzzling.

On Wed, Oct 2, 2013 at 9:15 PM, Albe Laurenz <laurenz.albe@wien.gv.at>wrote:

On 02/10/2013, at 6:49 PM, Tim Uckun <timuckun@gmail.com> wrote:

The reason for that is that in PostgreSQL there is no time zone

information stored along with a "timestamp with time zone",
it is stored in UTC.

That seems unintuitive. What is the difference between timestamp without time zone and timestamp with time zone? I was expecting to have the time zone stored in the field. For example one row might be in UTC but the other row might be in my local time.

Maybe the question I need to ask is "how can I store the time zone along with the timestamp"

That is because AT TIME ZONE returns a "timestamp without time zone"

Also seems counterintutive but I guess I can aways convert it. I am just not getting the right offset when I convert. That's what's puzzling.

Here's a handy blog post from Josh Berkus about timestamps:

http://it.toolbox.com/blogs/database-soup/zone-of-misunderstanding-48608

Cheers,

Tony

That's interesting article but it tells me that I can't really use the
timestamp with time zone data type. I really need to store the time zone
information along with the datetime and do not want to automatically
convert the timestamp to the connection time zone.

If one row has a timestamp in UTC and another has a timestamp in EST I want
the user (all users) to view the timestamps "as is" with the offset so they
can see how it may differ from each other and their own time zone.

I guess I have to store the time zones separately in another field.

On Wed, Oct 2, 2013 at 10:05 PM, Tony Theodore <tony.theodore@gmail.com>wrote:

Tim Uckun wrote:

The reason for that is that in PostgreSQL there is no time zone
information stored along with a "timestamp with time zone",
it is stored in UTC.

That seems unintuitive. What is the difference between timestamp without time zone and timestamp with
time zone? I was expecting to have the time zone stored in the field. For example one row might be in
UTC but the other row might be in my local time.

It is unintuitive and has caused many similar complaints
in the past, not least because other databases do it
differently.

The main difference between timestamp with time zone and
timestamp without is that the former will get converted
to your time zone (specified with the "TimeZone" parameter)
automatically, while the latter always looks the same.

Maybe the question I need to ask is "how can I store the time zone along with the timestamp"

Store an additional field "offset".
If you want to invest more energy and don't mind writing C,
you could create your own data type.

That is because AT TIME ZONE returns a "timestamp without time zone"

Also seems counterintutive but I guess I can aways convert it. I am just not getting the right offset
when I convert. That's what's puzzling.

I think that this is required by the SQL standard.

But think of it that way:
It is the answer to the question "What is 2013-10-02 00:00:00 UTC
in Vienna?"
The answer is not time zone dependent. It should be
"2013-10-02 02:00:00" and not "2013-10-02 02:00:00 CEST".

Yours,
Laurenz Albe

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

On Wed, Oct 02, 2013 at 09:49:38PM +1300, Tim Uckun wrote:

Maybe the question I need to ask is "how can I store the time zone along
with the timestamp"

You need an extra field, say, of type interval.

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

On Wed, Oct 02, 2013 at 09:49:38PM +1300, Tim Uckun wrote:

The reason for that is that in PostgreSQL there is no time zone

information stored along with a "timestamp with time zone",
it is stored in UTC.

A better name might perhaps been "timezone aware timestamp".

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

On Wed, Oct 02, 2013 at 09:18:30AM +0000, Albe Laurenz wrote:

Maybe the question I need to ask is "how can I store the time zone along with the timestamp"

Store an additional field "offset".
If you want to invest more energy and don't mind writing C,
you could create your own data type.

Might not a composite type (timestamp without timezone, interval) suffice ?

Or does that still need some C sprinkling (for operator support, say) ?

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

Karsten Hilbert wrote:

Maybe the question I need to ask is "how can I store the time zone along with the timestamp"

Store an additional field "offset".
If you want to invest more energy and don't mind writing C,
you could create your own data type.

Might not a composite type (timestamp without timezone, interval) suffice ?

Depends on what you want.

If all you want is store timestamp and time zone, a composite
type is fine.

Or does that still need some C sprinkling (for operator support, say) ?

Exactly. If you want "<" to work right for this data type
that's the road you have to go.

Yours,
Laurenz Albe

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

On Wed, Oct 02, 2013 at 11:48:02AM +0000, Albe Laurenz wrote:

Or does that still need some C sprinkling (for operator support, say) ?

Exactly. If you want "<" to work right for this data type
that's the road you have to go.

I see.

Whatever became of the 2011 intent to implement
the above that's linked to in the blog post ?

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

Karsten Hilbert wrote:

Whatever became of the 2011 intent to implement
the above that's linked to in the blog post ?

You'd have to ask Alvaro.

Yours,
Laurenz Albe

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

On Wed, Oct 02, 2013 at 02:09:23PM +0000, Albe Laurenz wrote:

Karsten Hilbert wrote:

Whatever became of the 2011 intent to implement
the above that's linked to in the blog post ?

You'd have to ask Alvaro.

I figured he'd maybe read this on-list :-)

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

On 10/02/2013 05:58 AM, Karsten Hilbert wrote:

On Wed, Oct 02, 2013 at 11:48:02AM +0000, Albe Laurenz wrote:

Or does that still need some C sprinkling (for operator support, say) ?

Exactly. If you want "<" to work right for this data type
that's the road you have to go.

I see.

Whatever became of the 2011 intent to implement
the above that's linked to in the blog post ?

Not sure about that, but I do remember this:

http://svana.org/kleptog/pgsql/taggedtypes.html

No indication of whether it will work with 9.x servers though.

Karsten

--
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 10/02/2013 04:19 AM, Karsten Hilbert wrote:

On Wed, Oct 02, 2013 at 09:49:38PM +1300, Tim Uckun wrote:

The reason for that is that in PostgreSQL there is no time zone

information stored along with a "timestamp with time zone",
it is stored in UTC.

A better name might perhaps been "timezone aware timestamp".

Karsten

The trouble is that it isn't timezone aware.

When I have to explain this I tend to tell people to mentally change
"timestamp with time zone" to "point-in-time". That "point-in-time" data
can be represented in many different formats and "localized" to
different zones but they are all the identical point-in-time.

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 10/02/2013 01:49 AM, Tim Uckun wrote:

The reason for that is that in PostgreSQL there is no time zone

information stored along with a "timestamp with time zone",
it is stored in UTC.

That seems unintuitive. What is the difference between timestamp
without time zone and timestamp with time zone? I was expecting to
have the time zone stored in the field. For example one row might be
in UTC but the other row might be in my local time.

Maybe the question I need to ask is "how can I store the time zone
along with the timestamp"

That is because AT TIME ZONE returns a "timestamp without time zone"

Also seems counterintutive but I guess I can aways convert it. I am
just not getting the right offset when I convert. That's what's puzzling.

As I mentioned in a separate reply, the best mental-model I've found for
the ill-named "timestamp with time zone" is "point in time."

If you also need the location (or just the time zone) of an event I
would recommend using two fields one of which is the event_timestamp as
a timestamp with time zone (point in time) and the other is the
event_timezone which is a text column with the full timezone name. You
can get a full list of recognized time-zone names with "select * from
pg_timezone_names".

I recommend storing the data as a timestamp with time zone and a full
time-zone name to avoid data ambiguity during daylight saving changes.
I.e. when the clock falls-back you will have 1:30 am twice if you are
storing a timestamp without time zone. This *may* be disambiguated if
you use an appropriate zone abbreviation like 1:30 EST vs 1:30 EDT but
abbreviations lead to other problems in worldwide data including the
problem that abbreviations may be reused leading to weirdness like
needing to set the AUSTRALIAN_TIMEZONES parameter properly to avoid
conflict with EST (Australian Eastern Standard Time) and EST (US Eastern
Standard Time) among others - this will be even more "fun" if trying to
select from a table that includes both Australian and United States data.

If you structure the data as recommended above you can simply get the
local time as:

SELECT ..., event_timestamp at time zone event_timezone as
event_local_time, ...

when you need the local time but you will still retain the exact
point-in-time for use as needed.

Cheers,
Steve

On Wed, Oct 02, 2013 at 10:40:36AM -0700, Steve Crawford wrote:

The reason for that is that in PostgreSQL there is no time zone

information stored along with a "timestamp with time zone",
it is stored in UTC.

A better name might perhaps been "timezone aware timestamp".

Karsten

The trouble is that it isn't timezone aware.

INSERT/UPDATE is, SELECT is not :-)

When I have to explain this I tend to tell people to mentally change
"timestamp with time zone" to "point-in-time". That "point-in-time"
data can be represented in many different formats and "localized" to
different zones but they are all the identical point-in-time.

That is, indeed, a helpful metaphor.

Maybe an explicit statement could be added to the docs
(I just checked 9.3) to the effect that "no, the input
time zone is NOT stored and can NOT be retrieved later,
think of it as a point-in-time".

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

I do think it would help to add it to the docs. The name "TIMESTAMP WITH
TIME ZONE" clearly implies the time zone is stored in the field. One day
there will really be a timestamp with time zone embedded in it field and I
wonder what they will call that.

On 10/02/2013 02:53 PM, Tim Uckun wrote:

I do think it would help to add it to the docs. The name "TIMESTAMP WITH
TIME ZONE" clearly implies the time zone is stored in the field. One
day there will really be a timestamp with time zone embedded in it field
and I wonder what they will call that.

I think you are fighting the standard here:

sql92
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

pg 31
"

Therefore, datetime data types that contain time fields (TIME and
TIMESTAMP) are maintained in Universal Coordinated Time (UTC),
with
an explicit or implied time zone part.

The time zone part is an interval specifying the difference
between
UTC and the actual date and time in the time zone represented by
the time or timestamp data item. The time zone displacement is
defined as

INTERVAL HOUR TO MINUTE

A TIME or TIMESTAMP that does not specify WITH TIME ZONE has
an im-
plicit time zone equal to the local time zone for the SQL-session.
The value of time represented in the data changes along with the
local time zone for the SQL-session. However, the meaning of the
time does not change because it is effectively maintained in UTC.

"

sql99

http://dbis-informatik.uibk.ac.at/files/ext/lehre/ss11/vo-ndbm/lit/ORel-SQL1999-IBM-Nelson-Mattos.pdf

pg 67
"
Coordinated universal time (UTC) used to store TIME and
TIMESTAMP values

WITH TIME ZONE can be specified

Each session has a time zone, which is used if no time zone is
explicitly specified

"
--
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