mark_postgres_user <mark.ikemoto@xxxxxxxxxxx> writes: > In my Postgres 9.1.9 database, I'd like to display a field I created, > â??start_timeâ??, in my â??miscâ?? database table. Iâ??ve defined this field as data > type â??timestamp without time zoneâ??. Everything that I've read about > Postgres says that that timestamp fields are stored in UTC format. So when > these fields are displayed theyâ??ll be converted at that point into local and > show up in local timezone time based on what my server is configured for > (currently PDT). Those statements apply to values of type timestamp WITH timezone. A timestamp WITHOUT timezone is just what-you-see-is-what-you-get. You can use AT TIME ZONE to rotate the value to another zone anyway, but it's a bit difficult to wrap your head around what will happen. IIRC, that operator will assume that the without-time-zone value is expressed in the time zone you name as the other operand, and rotate it from there to UTC, producing a value of timestamp WITH time zone. That's okay so far, but remember that when you display the result, it'll get rotated again (to whatever zone you have set in the "timezone" parameter). Generally speaking, if you're storing values that represent well-defined instants in time, you're best off using timestamp with timezone. Timestamp without timezone has some use to represent concepts like "the meeting is at 4pm local time Tuesday", where you're aware that this isn't actually a well-specified instant without some context; or if you really don't want the system doing *anything* for you time zone wise. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
