Обсуждение: Odd timezone backend output
I am confused about the below results. The backend is in EDT but it is converting timestamps into EST ... excluding NOW(). Regardless of the timezone provided, the backend is dishing out EST. [root@dev43 ~]# uname -a 2.6.9-67.0.4.EL #1 Sun Feb 3 06:53:29 EST 2008 i686 athlon i386 GNU/Linux [root@dev43 ~]# date Thu May 1 09:54:17 EDT 2008 postgres=# select version(); PostgreSQL 8.3devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-8) postgres=# set datestyle='Postgres, MDY'; postgres=# select now(); now ------------------------------------- Thu May 01 09:28:53.164084 2008 EDT postgres=# select '1997-01-29 12:31:42.92214 EDT'::timestamptz; timestamptz ------------------------------------ Wed Jan 29 11:31:42.92214 1997 EST postgres=# select '1997-01-29 12:31:42.92214 PST'::timestamptz; timestamptz ------------------------------------ Wed Jan 29 15:31:42.92214 1997 EST Is this expected behavior? I am not sure if I am missing something or my results are wrong. Is there a setting that needs tweaking? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/
Andrew Chernow <ac@esilo.com> writes:
> I am confused about the below results. The backend is in EDT but it is
> converting timestamps into EST ... excluding NOW(). Regardless of the
> timezone provided, the backend is dishing out EST.
Try a date that's actually during the EDT part of the year.
regards, tom lane
On Thu, May 01, 2008 at 09:53:41AM -0400, Andrew Chernow wrote: > I am confused about the below results. The backend is in EDT but it is > converting timestamps into EST ... excluding NOW(). Regardless of the > timezone provided, the backend is dishing out EST. First, this doesn't really belong on hackers, which is for hacking of the back end. If you're going to follow up, please take it to -general or something. Your client thinks it's in America/Toronto or something similar. You can set the time zone you're in by SET TIME ZONE (or timezone). There's a lot more about this in the manual at <http://www.postgresql.org/docs/8.3/interactive/datatype-datetime.html#DATATYPE-TIMEZONES> > postgres=# select now(); > now > ------------------------------------- > Thu May 01 09:28:53.164084 2008 EDT May is in Daylight time in (AFAIK all) Eastern zones. > postgres=# select '1997-01-29 12:31:42.92214 EDT'::timestamptz; > timestamptz > ------------------------------------ > Wed Jan 29 11:31:42.92214 1997 EST > > postgres=# select '1997-01-29 12:31:42.92214 PST'::timestamptz; > timestamptz > ------------------------------------ > Wed Jan 29 15:31:42.92214 1997 EST January is in Standard time in Eastern zones. Note that you asked for 1997-01-29 12:31:42.92214 EDT, and got back what time that would be _for your actual timezone_. Same thing for the Pacific case. A -- Andrew Sullivan ajs@commandprompt.com +1 503 667 4564 x104 http://www.commandprompt.com/
Tom Lane wrote: > Andrew Chernow <ac@esilo.com> writes: >> I am confused about the below results. The backend is in EDT but it is >> converting timestamps into EST ... excluding NOW(). Regardless of the >> timezone provided, the backend is dishing out EST. > > Try a date that's actually during the EDT part of the year. > > regards, tom lane > Different systems do different things with Daylight time. For instance: NTFS adjusts winter file times while in daylight savings (A file time of Jan 20 6PM reads Jan 20 7PM while in Daylight Time). Whether that is good or bad is a different story. I don't really have a problem with either, just needed a little clarity. Thanks, -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/
Andrew Chernow wrote: > Tom Lane wrote: >> Andrew Chernow <ac@esilo.com> writes: >>> I am confused about the below results. The backend is in EDT but it >>> is converting timestamps into EST ... excluding NOW(). Regardless >>> of the timezone provided, the backend is dishing out EST. >> >> Try a date that's actually during the EDT part of the year. >> >> regards, tom lane >> > > Different systems do different things with Daylight time. For > instance: NTFS adjusts winter file times while in daylight savings (A > file time of Jan 20 6PM reads Jan 20 7PM while in Daylight Time). > Whether that is good or bad is a different story. I don't really have > a problem with either, just needed a little clarity. > > The only file times we should ever be interested in are surely epoch times, which should be unaffected by time zones. cheers andrew
>>NTFS adjusts winter file times while in daylight savings > > The only file times we should ever be interested in are surely epoch > times, which should be unaffected by time zones. > > cheers > > andrew > epoch, or at least non-timezone adjusted times, is the way every modern FS stores file times, no one said otherwise. There is a big difference between time storage and display ... I was talking about display. Postgres deals with both so at some point, a file time (or any time) will get displayed. There are different ways of handling daylight display, I have yet to find a technically correct way of doing it. The more I think about it, I personally like the display behavior of NTFS file times over something like EXT3. When I am in EDT, it is useful to have all display times in that zone (regardless of whether that time falls within winter or summer) ... just as they would if I switched to PST. -- andrew chernow eSilo, LLC. --every bit counts
On Thu, May 01, 2008 at 09:58:09PM -0400, Andrew Chernow wrote: > The more I think about it, I personally like the display behavior of NTFS > file times over something like EXT3. When I am in EDT, it is useful to > have all display times in that zone (regardless of whether that time falls > within winter or summer) ... just as they would if I switched to PST. There's a difference between the two cases you're thinking of, though. In the case of "you being in EDT", you didn't actually switch time zone. In Eastern time zones, the offset is -05 from UTC for part of the year, and -04 from UTC from part of the year. That's what it _means_ to be in Eastern time: you didn't really switch time zone at all. I'm in America/Toronto in January and in June. My display should show the time correct to my time zone, not according to the offset from UTC. If you really want that, change your time zone to be an offset from UTC rather than a particular zone. (We used to have to differentiate between EST and EDT during the summer months, because Indiana didn't switch. As far as I know, there is no longer a single jurisdiction where the summer time change doesn't happen in Eastern time. If not for historical reasons, I'd argue the name should be changed to Eastern time.) If you switch to Pacific time (and why is it that people say "EDT" but "PST"? I don't know of any Pacific time jurisdictions that don't switch, either. Arizona doesn't switch mostly, but they're in Mountain time), the same thing happens. A -- Andrew Sullivan ajs@commandprompt.com +1 503 667 4564 x104 http://www.commandprompt.com/
Andrew Sullivan wrote: > On Thu, May 01, 2008 at 09:58:09PM -0400, Andrew Chernow wrote: > >> The more I think about it, I personally like the display behavior of NTFS >> file times over something like EXT3. When I am in EDT, it is useful to >> have all display times in that zone (regardless of whether that time falls >> within winter or summer) ... just as they would if I switched to PST. > > There's a difference between the two cases you're thinking of, though. > > In the case of "you being in EDT", you didn't actually switch time > zone. In Eastern time zones, the offset is -05 from UTC for part of > the year, and -04 from UTC from part of the year. That's what it > _means_ to be in Eastern time: you didn't really switch time zone at > all. I'm in America/Toronto in January and in June. My display > should show the time correct to my time zone, not according to the > offset from UTC. If you really want that, change your time zone to be > an offset from UTC rather than a particular zone. (We used to have to > differentiate between EST and EDT during the summer months, because > Indiana didn't switch. As far as I know, there is no longer a single > jurisdiction where the summer time change doesn't happen in Eastern > time. If not for historical reasons, I'd argue the name should be > changed to Eastern time.) > > If you switch to Pacific time (and why is it that people say "EDT" but > "PST"? I don't know of any Pacific time jurisdictions that don't > switch, either. Arizona doesn't switch mostly, but they're in > Mountain time), the same thing happens. > > A > I prefer offset from UTC, the timezone abbrevs are ambiguos and confusing. If I am in a timezone that is currently 4 hours behind UTC, I would prefer all times to display adjusted by that offset. I understand that technically its eastern time and EDT makes no sense in Jan (NOTE: linux 'date' command rejects this ... should the backend?), but its friendlier to adjust by a single offset (at least me thinks). The individual's perception of time is -0400, thus displaying all times adjusted by that seems logical. My whole preference here is in regards to display times. I always try to see things from a non-technical end-user's perspective. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/
Andrew Chernow wrote: > > I prefer offset from UTC, the timezone abbrevs are ambiguos and > confusing. If I am in a timezone that is currently 4 hours behind > UTC, I would prefer all times to display adjusted by that offset. I > understand that technically its eastern time and EDT makes no sense in > Jan (NOTE: linux 'date' command rejects this ... should the backend?), > but its friendlier to adjust by a single offset (at least me thinks). > The individual's perception of time is -0400, thus displaying all > times adjusted by that seems logical. My whole preference here is in > regards to display times. I always try to see things from a > non-technical end-user's perspective. > Well, I think you're failing in this instance :-). If I see a time that says 4.00 am June 27th tzname I understand it to be with the offset that was current in that time zone *at that time*. And I certainly don't want it to change depending on what the *current* offset is in that time zone - I want it always to display the same. I bet if you take a random sample of non-technical end-users you'll find that's exactly what the vast majority want. cheers andrew
On Fri, May 02, 2008 at 09:54:20AM -0400, Andrew Chernow wrote: > I prefer offset from UTC, the timezone abbrevs are ambiguos and confusing. So did you read the manual on SET TIME ZONE? > TIME ZONE > SET TIME ZONE value is an alias for SET timezone TO value. The > syntax SET TIME ZONE allows special syntax for the time zone > specification. Here are examples of valid values: > 'PST8PDT' > The time zone for Berkeley, California. > 'Europe/Rome' > The time zone for Italy. > -7 > The time zone 7 hours west from UTC (equivalent to > PDT). Positive values are east from UTC. INTERVAL '-08:00' HOUR > TO MINUTE > The time zone 8 hours west from UTC (equivalent to PST). A -- Andrew Sullivan ajs@commandprompt.com +1 503 667 4564 x104 http://www.commandprompt.com/