From: | Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> |
---|---|
To: | hernan gonzalez <hgonzalez(at)gmail(dot)com> |
Cc: | Steve Crawford <scrawford(at)pinpointresearch(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: to_timestamp() and timestamp without time zone |
Date: | 2011-06-23 19:15:43 |
Message-ID: | 4E0390DF.5090502@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 06/23/2011 11:40 AM, hernan gonzalez wrote:
> Rather than being not viable, I'd argue that is is not correct.
> Rather, a simple direct cast will suffice:
> '2011-12-30 00:30:00'::timestamp without time zone
>
>
> That works only for that particular format. The point is that, for
> example, if I have some local date time
> stored as a string in other format ('30/12/2011 00:30:00') I cannot
> reliably parse it as a TIMESTAMP. Which I should.
Works here. I am in US PDT:
select to_timestamp('30/12/2011 00:30:00', 'DD/MM/YYYY HH24:MI:SS
')::timestamp with time zone;
to_timestamp
------------------------
2011-12-30 00:30:00-08
>
> Every feature and function in PostgreSQL is "potentially dangerous"
> - understanding them and using them correctly is the responsibility
> of the programmer. Time handling has lots of subtleties that take
> time to digest
>
>
> Thanks for the advice. But it's precisely in the role of a programmer
> who has digested a good deal about date-time data and its subtleties,
> and who is trying to use in a consistent an robust way date-time data
> that I'm asking this question. Or rather, reporting this issue.
>
> . It appears that you would like a timestamp of 2011-12-30 00:30:00
> which you can get. But even so, there are places in the world where
> that time exists and other places in the world that it does not.
>
> If you try to force that timestamp into a zone where it doesn't
> exist, PostgreSQL makes a reasonable interpretation of the intended
> point in time.
>
>
> I strongly disagree. I'm not trying "to force that timestamp into a
> zone" at all. I'm just telling postgresl to parse the string '30/12/2011
> 00:30:00' as a TIMESTAMP (without time zone), that is, to
> parse/understand/store it as the abstract/civil (wall calendar+clock)
> local datetime "30 dec 2011, 00 30 00 am" with NO association with a
> timezone.
Again works here:
test=> select to_timestamp('30/12/2011 00:30:00', 'DD/MM/YYYY HH24:MI:SS
')::timestamp without time zone;
to_timestamp
---------------------
2011-12-30 00:30:00
Postgreql does not need to interpret anything here, and indeed
> it works pefectly with this datetime if I store it in a TIMESTAMP
> WITHOUT TIMEZONE (it stores/manipulates it internally as UTC, but the
> programmer doesn't care about it, that is internal).
Actually that is how timestamp with timezone are stored:) If you don't
want to deal with time zones keep tz out of the loop. Store the values
in timestamp without time zone. If you at any point store it in a
timestamp with timezone or cast it to same you will change the value
based on whatever offset is in effect at that time. That is what is
supposed to happen.
> IT's only this particular function TO_TIMESTAMP() that have this
> problem, because it insists in "interpret" the local date time as a
> datetime with timezone (and can't even tell it to use UTC). This is just
> wrong.
Yes you can:
test=> select to_timestamp('30/12/2011 00:30:00', 'DD/MM/YYYY HH24:MI:SS
') at time zone 'UTC';
timezone
---------------------
2011-12-30 08:30:00
> Hernán
--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | hernan gonzalez | 2011-06-23 19:30:24 | Re: to_timestamp() and timestamp without time zone |
Previous Message | hernan gonzalez | 2011-06-23 18:40:51 | Re: to_timestamp() and timestamp without time zone |