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

> >> Looks to me like an off-by-one kind of problem in deciding which
> >> timezone applies to midnight of a transition day.
> > The date->timestamp conversion code gets this right, so you might want
> > to look at that.
> Au contraire: the cited examples appear to prove that the
> date->timestamp conversion code gets this *wrong*. Or did
> you miss the point of
> regression=# select '2000-04-02'::date::timestamp;
> ?column?
> ------------------------
> 2000-04-01 23:00:00-05
> (1 row)

*sigh* Well, of course I missed the point. Thanks for clarifying that ;)

OK, the situation is coming back to me now. The conversion is done in
steps:

1) Check that the date is likely to be within the Unix system time
range. Otherwise, do all conversions/calculations in UTC.

2) Convert to an integer "Unix system time".

3) Rotate by 12 hours (to UTC noon!). This is supposed to ensure that we
stay in the correct day after conversion to local time *no matter what
time zone we are actually in*, but is likely the problem in this edge
case.

4) Call localtime() to fill in the fields of a tm structure. This is how
I get ahold of the time zone (which is not known before this step). For
this DST edge case, the time zone is off by one hour :(

5) Copy the fields from the result of the call to localtime() into a new
tm structure, with zeros for time fields.

6) Call tm2timestamp(), which recalculates an internal UTC-based
timestamp value based on the current contents of the tm structure.

It *may* be possible to run through some of these steps *twice*, to
verify that the conversion to/from the tm structure including time zone
info gives a result on an even day boundary. But it sounds expensive.

Comments and suggestions are appreciated...

- Thomas