Re: to_timestamp() and timestamp without time zone - Mailing list pgsql-general

From hernan gonzalez
Subject Re: to_timestamp() and timestamp without time zone
Date
Msg-id BANLkTikb1_A6GjmuVPxzDvzbjR=pJ3=yvA@mail.gmail.com
Whole thread Raw
In response to Re: to_timestamp() and timestamp without time zone  (Steve Crawford <scrawford@pinpointresearch.com>)
Responses Re: to_timestamp() and timestamp without time zone
List pgsql-general

Every example here starts, at its core, with to_timestamp. That function returns a timestamp *with* time zone so of-course the current timezone setting will influence it. Stop using it - it doesn't do what you want.

If you cast directly to a timestamp *without* time zone you can take advantage of the many formats PostgreSQL supports.

See: http://www.postgresql.org/docs/9.0/interactive/datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE for supported formats. Note also that you can use "set datestyle" to match your MDY or DMY date formatting.


Fair enough: to_timestamp doesn't do what I want, I must resort to casting.

But it's rather unfortunate that a to_timestamp() function doesnt actually parse a "timestamp" (which, is a alias  to "timestamp without timezone", at least since PG 7.3) .  

BTW, the "set datestyle" workaround can be umpractical in some scenarios (case in point: I have a view that returns some column as "timestamp" by doing that parsing from a text colum ) I even can't put a "SET DATESTYLE" (or a SET TIMEZONE=UTC)" in a function, unless I make it VOLATILE, which I certainly would not like.

Regards

Hernán J. González

pgsql-general by date:

Previous
From: Tomas Vondra
Date:
Subject: Re: unique across two tables
Next
From: "David Johnston"
Date:
Subject: Re: to_timestamp() and timestamp without time zone