How Can I Discard Milliseconds from a Timestamp in SQL?

Discarding Milliseconds from a Timestamp without a Timezone

The millisecond and second parts of a timestamp can be discarded or rounded using different methods.

Rounding to Full Seconds

To round the timestamp to full seconds, a cast to timestamp(0) or timestamptz(0) can be applied. This method effectively removes any fractional parts.

SELECT now()::timestamp(0);

Truncating to Seconds

If truncating the timestamp to seconds is desired (keeping the seconds intact), the date_trunc() function is recommended.

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

date_trunc() accepts various input options for the first argument, known as the "field." By specifying 'minute' as the field, the timestamp can be truncated to the minute, effectively discarding the seconds and milliseconds.

The return value of both the cast and date_trunc() function matches the input data type, whether it be timestamp, timestamptz, or interval.

The above is the detailed content of How Can I Discard Milliseconds from a Timestamp in SQL?. For more information, please follow other related articles on the PHP Chinese website!