Thread: Timestamp with and without timezone conversion confusion.
I have the following query.
with parsed_data as (
SELECT
devicereportedtime ,
DATE_TRUNC('minute', devicereportedtime - (EXTRACT(minute FROM devicereportedtime)::integer % 5 || 'minutes')::interval) as interval_start
FROM systemevents
WHERE devicereportedtime >= now() - interval '10 minutes'
ORDER BY devicereportedtime asc
limit 10000
),
grouped_data as (
SELECT
interval_start at time zone 'Etc/UTC' as interval_start,
MIN(devicereportedtime) at time zone 'Etc/UTC' as min_datetime,
MAX(devicereportedtime) at time zone 'Etc/UTC' as max_datetime
FROM parsed_data
GROUP BY interval_start
)
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
The field "devicereportedtime" is timstamp without time zone. The database is set to 'Pacific/Auckland" (my development machine) and the devices are reporting UTC.
The grouped_data clause forces the parsing of the timestamp without timestamp to be UTC as well as the mins and the max timestamps. I then want to present this data in other time zones
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","max_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? 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.
So what am I doing wrong here?
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
>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.
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:
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 resultsThe configuration parameter "TimeZone" determines how "timestamp with
>
> "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?
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.That is because AT TIME ZONE returns a "timestamp without time zone"
> 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.
in this case, see the documentation.
Yours,
Laurenz Albe
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:
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:
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:Cheers,Tony
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
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
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
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
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
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
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
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
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
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
On 10/02/2013 01:49 AM, Tim Uckun wrote:
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.">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.
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
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