How Can I Remove Milliseconds and Seconds from a Timestamp in SQL?

Removing Milliseconds and Seconds from a Timestamp

When working with timestamps, it is often useful to discard the millisecond or even second portions of the value. This can be achieved through various approaches.

Rounding to Full Seconds

If the goal is to round a timestamp to full seconds, without regard to time zone, a cast to timestamp(0) or timestamptz(0) can be employed.

SELECT now()::timestamp(0);

This operation effectively rounds the timestamp to the nearest whole second. It's important to note that this approach does not truncate the value; it rounds it.

Truncation

If truncation is desired, where milliseconds and seconds are removed without rounding, the date_trunc() function can be utilized.

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

This function takes two arguments:

• The "field" specifying the granularity of the truncation (e.g., 'second', 'minute')
• The timestamp or date value to truncate

The result is a truncated timestamp that reflects the specified granularity.

Input and Output Data Types

It's crucial to note that the data type of the return value matches the input. For example:

• timestamp(0) will return a timestamp
• timestamptz(0) will return a timestamp with time zone
• interval will return an interval

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