Re: Timestamp with and without timezone conversion confusion.
| От | Tim Uckun |
|---|---|
| Тема | Re: Timestamp with and without timezone conversion confusion. |
| Дата | |
| Msg-id | CAGuHJrNvGTTg2PQmh-eAFhWhGtBa+LcKHG+=WiJaxmY319mTiQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Timestamp with and without timezone conversion confusion. (Albe Laurenz <laurenz.albe@wien.gv.at>) |
| Ответы |
Re: Timestamp with and without timezone conversion confusion.
(Tony Theodore <tony.theodore@gmail.com>)
Re: Timestamp with and without timezone conversion confusion. (Albe Laurenz <laurenz.albe@wien.gv.at>) Re: Timestamp with and without timezone conversion confusion. (Karsten Hilbert <Karsten.Hilbert@gmx.net>) Re: Timestamp with and without timezone conversion confusion. (Karsten Hilbert <Karsten.Hilbert@gmx.net>) Re: Timestamp with and without timezone conversion confusion. (Steve Crawford <scrawford@pinpointresearch.com>) |
| Список | 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.
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
В списке pgsql-general по дате отправления:
Следующее
От: Tony TheodoreДата:
Сообщение: Re: Timestamp with and without timezone conversion confusion.