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 ?

回答1:

Cast it to timestamp(0) or timestamptz(0) to remove fractional seconds:

SELECT now()::timestamp(0);

Like @karlgold commented, this rounds to full seconds, and fractions are not stored if you keep operating with this data type.

Use date_trunc() for more specific needs.

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

This truncates (leaves seconds unchanged). Further manipulation can re-introduce fractional seconds.

Of course, you can combine both - this time removing the second part as well:

SELECT date_trunc('minute', now())::timestamp(0);

But be aware that assigning this value to a column or variable of a different data type may result in another assignment cast.