From: | hernan gonzalez <hgonzalez(at)gmail(dot)com> |
---|---|
To: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: to_timestamp() and timestamp without time zone |
Date: | 2011-06-23 18:40:51 |
Message-ID: | BANLkTimvJUfEo2kTvTHzhJJ5gPoj_ed_GA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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.
> 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. 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).
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.
Hernán
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2011-06-23 19:15:43 | Re: to_timestamp() and timestamp without time zone |
Previous Message | Edoardo Panfili | 2011-06-23 18:39:05 | Re: unique across two tables |