Re: to_timestamp() and timestamp without time zone
От | Adrian Klaver |
---|---|
Тема | Re: to_timestamp() and timestamp without time zone |
Дата | |
Msg-id | 4E0390DF.5090502@gmail.com обсуждение исходный текст |
Ответ на | Re: to_timestamp() and timestamp without time zone (hernan gonzalez <hgonzalez@gmail.com>) |
Ответы |
Re: to_timestamp() and timestamp without time zone
(hernan gonzalez <hgonzalez@gmail.com>)
Re: to_timestamp() and timestamp without time zone ("David Johnston" <polobo@yahoo.com>) |
Список | 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.klaver@gmail.com
В списке pgsql-general по дате отправления: