pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddate_trunc should be called date_round?
3+ messages / 2 participants
[nested] [flat]
* date_trunc should be called date_round?
@ 2009-06-29 08:47 Mario Splivalo <mario.splivalo@megafon.hr>
0 siblings, 1 reply; 3+ messages in thread
From: Mario Splivalo @ 2009-06-29 08:47 UTC (permalink / raw)
To: pgsql-sql
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.7 on 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: date_trunc should be called date_round?
@ 2009-06-29 14:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mario Splivalo <mario.splivalo@megafon.hr>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2009-06-29 14:25 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@megafon.hr>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: date_trunc should be called date_round?
@ 2009-06-30 06:51 Mario Splivalo <mario.splivalo@megafon.hr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Mario Splivalo @ 2009-06-30 06:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-06-30 06:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-06-29 08:47 date_trunc should be called date_round? Mario Splivalo <mario.splivalo@megafon.hr>
2009-06-29 14:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-06-30 06:51 ` Mario Splivalo <mario.splivalo@megafon.hr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox