By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,081 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

rounding timestamps

P: n/a
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.

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

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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*******@postgresql.org

Nov 12 '05 #2

P: n/a
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/curre...-datetime.html

HTH,

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

Nov 12 '05 #3

P: n/a
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/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), '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.

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

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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.