2

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Anand Kumar
  • 145
  • 1
  • 3
  • 16
  • Do you mean to *round* or *truncate* like your example indicates? Also, `timestamp(0)` does not store milliseconds to begin with. That's what the appended `(0)` is for. Do you maybe have a different source type, and only the target is `timestamp(0)` and want to truncate instead of round in the assignment? Or is it a display issue? – Erwin Brandstetter Mar 05 '19 at 19:32

2 Answers2

4

You can use the function date_trunc:

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

Lorenz Henk
  • 771
  • 5
  • 13
  • this will display 2018-04-19 10:43:13 only milliseconds discarded. millisecond value should be round off. – Anand Kumar Mar 05 '19 at 12:32
  • 1
    The milliseconds are only shown if they are set. If you really want to display `.000` at the end, you can do `truncated_ts::text || '.000'`. – Lorenz Henk Mar 05 '19 at 12:36
2

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228