Thread: date_trunc should be called date_round?
It's stated in the docs that date_trunc is "conceptually similar to the trunc function for numbers.". So, trunc(1.1) = 1, trunc(1.9) = 1, and so on. But, date_trunc behaves like round function: round(1.9) = 2. Example: idel=# select date_trunc('milliseconds', '2009-01-01 12:15:00.000999+02'::timestamp with time zone); date_trunc ----------------------------2009-01-01 11:15:00.001+01 (1 row) fidel=# select version(); version ----------------------------------------------------------------------------------------------------------PostgreSQL 8.3.7on x86_64-unknown-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu 4.3.3-5ubuntu4) 4.3.3 (1 row) Or am I again completely misreading something? Mike
Mario Splivalo <mario.splivalo@megafon.hr> writes: > But, date_trunc behaves like round function: round(1.9) = 2. Hmm ... only for float timestamps, and only for the millisec/microsec cases. case DTK_MILLISEC: #ifdef HAVE_INT64_TIMESTAMP fsec = (fsec / 1000) * 1000; #else fsec = rint(fsec * 1000) / 1000; #endif break; case DTK_MICROSEC: #ifndef HAVE_INT64_TIMESTAMP fsec = rint(fsec * 1000000) / 1000000; #endif break; I wonder if we should change this to use floor() instead. regards, tom lane
Tom Lane wrote: > Mario Splivalo <mario.splivalo@megafon.hr> writes: >> But, date_trunc behaves like round function: round(1.9) = 2. > > Hmm ... only for float timestamps, and only for the millisec/microsec > cases. > > case DTK_MILLISEC: > #ifdef HAVE_INT64_TIMESTAMP > fsec = (fsec / 1000) * 1000; > #else > fsec = rint(fsec * 1000) / 1000; > #endif > break; > case DTK_MICROSEC: > #ifndef HAVE_INT64_TIMESTAMP > fsec = rint(fsec * 1000000) / 1000000; > #endif > break; > > I wonder if we should change this to use floor() instead. > I guess it's safe, since you don't have negative timestamps (right?) or parts of timestamps (millisecs/microsecs), so floor() would act as trunc. Esp. if for the other parts of timestamp (days, hours, ...) it's actualy truncating, not rounding, i.e.: date_trunc('minute', '2009-01-01 12:13:50'::timestamp) would return '2009-01-01 13:13:00', not '2009-01-01 13:14:00'. One would expect similar behavior for the milli/microsec part. Now it's truncating, unless dealing with milli/microseconds, where it's rounding. Mike