-1

I have executed the following in my postgres database

SET TIME ZONE 'UTC';

When I execute the following it shows UTC as the timezone.

show timezone;

SELECT NOW();

Next I want to query a table. My table has several columns out of which some of them are date columns. If I query that table, will the date columns present the date in UTC? what is there is no time and just the date in the column. How does this timezone setting work?

user2158451
  • 1
  • 1
  • Did you read the docs? http://www.postgresql.org/docs/9.2/interactive/datatype-datetime.html – leonbloy Apr 30 '13 at 15:54
  • I provided a detailed explanation of how Postgres handles time zones in this related answer: http://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql/9576170#9576170. I don't know about `Vertica`. You should ask **separate questions** in any case. – Erwin Brandstetter Apr 30 '13 at 21:34

1 Answers1

1

In Postgresql, SET TIME ZONE 'UTC' only sets the "current" (session scope) timezone.

Obviously, the timezone has no effect on the date datatype. Besides this, Postgresql has two date-with-time datatypes: 

timestamptz = timestamp with time zone

timestamp   = timestamp without time zone

Actually, none of them stores a timezone, but they differ in how the "current timezone" influence them. Briefly, a timestamp (with time zone) input requires a timezone , if none is given the default is the "current timezone"; and its output is written using the "current timezone". A timestamp *without* time zone does not use a timezone for input or output.

For details, read the docs.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
leonbloy
  • 73,180
  • 20
  • 142
  • 190
  • 1
    The timezone setting can affect dates in as much that it can affect what CURRENT_DATE reports. So you need to be aware of that if asking for a report with "last 30 days" of data or some such. – Richard Huxton Apr 30 '13 at 18:29
  • @RichardHuxton: Yes, that's true, good point. But it doesn't affect the output of stored dates, nor the input of literal dates. – leonbloy Apr 30 '13 at 18:48
  • Indeed not '2013-03-28' is always less than '2013-03-29'. It's just the interaction that can catch you (or for that matter me) out. – Richard Huxton Apr 30 '13 at 19:42