Search code examples
sqlpostgresqlcastingtimestamprounding

Discard millisecond part from timestamp

How can I discard/round the millisecond part, better if the second part is also removed from a timestamp w/o timezone ?

Solution

  • A cast to timestamp(0) or timestamptz(0) rounds to full seconds:

    SELECT now()::timestamp(0);

    Fractions are not stored in table columns of this type.

    The function date_trunc() truncates (leaves seconds unchanged) - which is often what you really want:

    SELECT date_trunc('second', now()::timestamp);

    date_trunc accepts various input for the the first argument "field". Use 'minute' to truncate to the minute (also discard seconds).

    The data type of return values matches the input, i.e. timestamp / timestamptz / interval accordingly.