CODEWITHSUNDEEP
postgresqltimestamprounding
Anand Kumar
Anand Kumar

Reputation: 165

How to round off milliseconds value from timestamp(0) in PostgreSQL?

I need to round off milliseconds value from timestamp(0) without time zone.

Ex: 2018-04-19 10:43:13.719 to 2018-04-19 10:43:13.000

Upvotes: 2

Views: 2336

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656596

None of this is applicable to timestamp(0) like you suggested, since that type does not store milliseconds to begin with. Would make sense for literal input or timestamp (without precision modifier).

Rounding the timestamp '2018-04-19 10:43:13.719' would give you '2018-04-19 10:43:14' (rounded up).

To truncate microseconds, you can use date_trunc(), like Lorenz already provided. According to your comment you still want the redundant dangling '.000'. For display I suggest to_char():

SELECT to_char(date_trunc('seconds', timestamp '2018-04-19 10:43:13.719'), 'YYYY-MM-DD HH24:MI:SS.MS')

Or cheaper, append '.000' instead of actual milliseconds with to_char() directly:

SELECT to_char(timestamp '2018-04-19 10:43:13.719', 'YYYY-MM-DD HH24:MI:SS".000"')

The manual:

Ordinary text is allowed in to_char templates and will be output literally. You can put a substring in double quotes to force it to be interpreted as literal text even if it contains template patterns.

Upvotes: 2

Lorenz Henk
Lorenz Henk

Reputation: 781

You can use the function date_trunc:

SELECT date_trunc('seconds', '2018-04-19 10:43:13.719'::timestamp);

Upvotes: 4

Related Questions