Skip site navigation (1) Skip section navigation (2)

Re: bug in date_part() function in 6.5.2, 7.0.2

From: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
To: Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, analyst(at)sibinet(dot)ru, pgsql-bugs(at)postgresql(dot)org
Subject: Re: bug in date_part() function in 6.5.2, 7.0.2
Date: 2000-09-07 06:07:43
Message-ID: 39B730AF.BBEC447E@alumni.caltech.edu (view raw or flat)
Thread:
Lists: pgsql-bugs
> >  ....it's not date_part() bug, it's to_date() bug:
> Looks to me like an off-by-one kind of problem in deciding which
> timezone applies to midnight of a transition day.

Probably a bit worse (but no problem to solve ;): you need to make sure
that you rotate the date type to the correct time zone when you do the
conversion to timestamp. If you just blast the yy/mm/dd into the time
structure, leaving the time zone fields zero'd out, then you will be
doing the conversion in UTC. When the timestamp is read back out it is
converted to your local time zone.

The date->timestamp conversion code gets this right, so you might want
to look at that. From my testing, the only annoying case is for 02:00 on
the day of a DST transition, when you either skip or get an extra hour.

If you still have trouble, I'd be happy to look at your code...

                    - Thomas

In response to

Responses

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2000-09-07 11:21:57
Subject: How to connect to a remote database
Previous:From: Tom LaneDate: 2000-09-07 06:05:49
Subject: Re: bug in date_part() function in 6.5.2, 7.0.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group