How Can I Remove Milliseconds and Seconds from Timestamps in SQL?

Discarding Millisecond and Second Parts from Timestamps

When working with timestamps, there are often scenarios where you may need to remove the millisecond and/or second components. Here are two methods to achieve this:

1. Casting to Timestamp(0)

Casting a timestamp to timestamp(0) or timestamptz(0) rounds the value to full seconds, effectively discarding the millisecond and second parts:

SELECT now()::timestamp(0);

2. Using the date_trunc() Function

The date_trunc() function allows you to truncate a timestamp to a specified interval. By specifying 'second' as the first argument, you can truncate the timestamp to the full second:

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

You can also use 'minute' as the first argument to truncate the timestamp to the next full minute, discarding both seconds and milliseconds.

Note:

• Both methods will return a timestamp of the same type as the input (timestamp, timestamptz, or interval).
• The fraction of a second is not stored in table columns of type timestamp(0) or timestamptz(0).
• The date_trunc() function supports various intervals, allowing you to truncate to any desired precision.

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