rounding timestamps

[Claudio Lapidus]

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(timesta mp '2003-10-24 15:30:59.999',
'YYYYMMDDHH24MI SS');
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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Alvaro Herrera]

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"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Joe Conway]

Claudio Lapidus wrote:

test=> select to_char(timesta mp '2003-10-24 15:30:59.999',
'YYYYMMDDHH24MI SS');
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(timesta mp(0) '2003-10-24
15:30:59.999',' YYYYMMDDHH24MIS S');
to_char
----------------
20031024153100
(1 row)

See:
http://www.postgresql.org/docs/curre...-datetime.html

HTH,

Joe
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Claudio Lapidus]

Joe Conway wrote:

Is this what you wanted?

regression=# select to_char(timesta mp(0) '2003-10-24
15:30:59.999',' YYYYMMDDHH24MIS S');
to_char
----------------
20031024153100
(1 row)
Yes! Exactly!

See:
http://www.postgresql.org/docs/curre...-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), 'YYYYMMDDHH24MI SS') 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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html