> Sample Code
> create table oops (date date);
>
> insert into oops (date) values (to_date('24.03.2000','dd.mm.yyyy'));
> insert into oops (date) values (to_date('25.03.2000','dd.mm.yyyy'));
> insert into oops (date) values (to_date('26.03.2000','dd.mm.yyyy'));
> insert into oops (date) values (to_date('27.03.2000','dd.mm.yyyy'));
>
> select
> date::date
> ,date_part('day',date::date)
> ,date_part('dow',date::date)
> from oops
> order by date;
>
> ?column? | date_part | date_part
> ------------+-----------+-----------
> 24.03.2000 | 24 | 5
> 25.03.2000 | 25 | 6
> 26.03.2000 | 25 | 6
> 27.03.2000 | 27 | 1
> (4 rows)
Interesting...
test=# select date::timestamp, date_part('day', date), to_char(date, 'DD')
from oops;
?column? | date_part | to_char
------------------------+-----------+---------
2000-03-24 00:00:00+01 | 24 | 24
2000-03-25 00:00:00+01 | 25 | 25
2000-03-25 23:00:00+01 | 25 | 25
2000-03-27 00:00:00+02 | 27 | 27
(4 rows)
to_char() and date_part() has different code but some result, a problem
can't be in date_part()...
test=# select date::timestamp from oops;
?column?
------------------------
2000-03-24 00:00:00+01
2000-03-25 00:00:00+01
2000-03-25 23:00:00+01
^^^^
????
2000-03-27 00:00:00+02
(4 rows)
....it's not date_part() bug, it's to_date() bug:
test=# select to_date('26.03.2000','dd.mm.yyyy')::timestamp;
?column?
------------------------
2000-03-25 23:00:00+01
^^^
! Bug !
test=# select to_timestamp('26.03.2000','dd.mm.yyyy');
to_timestamp
------------------------
2000-03-26 00:00:00+01
^^^
! correct !
But to_date() call to_timestamp() only:
Datum
to_date(PG_FUNCTION_ARGS)
{
/* Quick hack: since our inputs are just like to_timestamp,
* hand over the whole input info struct...
*/
return DirectFunctionCall1(timestamp_date, to_timestamp(fcinfo));
}
Comments, some idea?
Karel
