How Can I Trim Milliseconds from Timestamps in SQL?

Trimming Timestamp to Truncate Milliseconds

When working with timestamps, it may be necessary to discard or round the millisecond part of the timestamp. For instance, removing fractions of a second can be useful for simplifying data analysis or improving performance in certain scenarios.

One method to accomplish this is using a cast to timestamp(0) or timestamptz(0). This operation rounds the timestamp to the nearest full second:

SELECT now()::timestamp(0);

The resulting value will have its milliseconds truncated to zero. However, note that this approach truncates microseconds and fractional seconds as well.

Another option is to utilize the date_trunc() function, which provides more granular control over the truncation process. The function takes two arguments: the field to truncate (e.g., 'second') and the timestamp to truncate. By specifying 'second' as the field, you can remove milliseconds without altering seconds:

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

The date_trunc() function supports a variety of fields for truncation, including 'minute' and 'hour'. This allows for greater flexibility in controlling the level of precision desired.

It's important to note that the data type of the return value in both cases matches the input. If the original timestamp is a timestamp or timestamptz, the result will also be of the same type.

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