Postgres Pro
Downloads
Home   >   mailing lists

Thread: rounding timestamps

rounding timestamps

From
"Claudio Lapidus"
Date:
Hello

(Sorry about reposting, but I'm still not arriving to any good solution for
this one)

I need to output a timestamp attribute formatted to fixed-width, no spaces
nor separators, something like

test=> select to_char(timestamp '2003-10-24 15:30:59.999',
'YYYYMMDDHH24MISS');
    to_char
----------------
 20031024153059
(1 row)

But my problem is that to_char truncates the fractional seconds, and I need
to round the value to the nearest integer second. In the above, I would need
the output rounded to 15:31:00, which is just a millisecond away, for
example. But I couldn't find a function to round a timestamp. Are there any
workaround?

thanks
cl.

Re: rounding timestamps

From
Alvaro Herrera
Date:
On Mon, Nov 24, 2003 at 12:15:54AM -0300, Claudio Lapidus wrote:

> But my problem is that to_char truncates the fractional seconds, and I need
> to round the value to the nearest integer second. In the above, I would need
> the output rounded to 15:31:00, which is just a millisecond away, for
> example. But I couldn't find a function to round a timestamp. Are there any
> workaround?

Maybe you can try with EXTRACT(epoch FROM timestamp), rounding that, and
then converting back to a timestamp through abstime.  Ugly though ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Hoy es el primer día del resto de mi vida"

Re: rounding timestamps

From
Joe Conway
Date:
Claudio Lapidus wrote:
> test=> select to_char(timestamp '2003-10-24 15:30:59.999',
> 'YYYYMMDDHH24MISS');
>     to_char
> ----------------
>  20031024153059
> (1 row)
>
> But my problem is that to_char truncates the fractional seconds, and I need
> to round the value to the nearest integer second. In the above, I would need
> the output rounded to 15:31:00, which is just a millisecond away, for
> example. But I couldn't find a function to round a timestamp. Are there any
> workaround?

Is this what you wanted?

regression=# select to_char(timestamp(0) '2003-10-24
15:30:59.999','YYYYMMDDHH24MISS');
     to_char
----------------
  20031024153100
(1 row)

See:
http://www.postgresql.org/docs/current/static/datatype-datetime.html

HTH,

Joe

Re: rounding timestamps

From
"Claudio Lapidus"
Date:
Joe Conway wrote:
> Is this what you wanted?
>
> regression=# select to_char(timestamp(0) '2003-10-24
> 15:30:59.999','YYYYMMDDHH24MISS');
>      to_char
> ----------------
>   20031024153100
> (1 row)

Yes! Exactly!

>
> See:
> http://www.postgresql.org/docs/current/static/datatype-datetime.html

Shame on me. I've must read that page more times than I can remember. I
never realized that I could use the precision qualifier to do a cast (and
round):

comp_20031117=> create table ts (ts timestamp without time zone);
CREATE TABLE
comp_20031117=> insert into ts values ('2003-10-24 15:30:59.999');
INSERT 406299 1
comp_20031117=> select * from ts;
           ts
-------------------------
 2003-10-24 15:30:59.999
(1 row)

comp_20031117=> select to_char (ts ::timestamp(0), 'YYYYMMDDHH24MISS') from
ts;
    to_char
----------------
 20031024153100
(1 row)

thank you very much Joe
cl.

PS. Alvaro, your solution was what I was implementing already, but yes it's
ugly, that's why I gave it a second round. Thanks anyway.