从时间戳中丢弃毫秒部分

如果 second部分也从 timestamp/o timezone中删除，我怎样才能更好地丢弃/绕过 millisecond部分？

89472 次浏览

小开

最佳答案

A cast to timestamp(0) or timestamptz(0) rounds to full seconds:

SELECT now()::timestamp(0);

Fractions are not stored in table columns of this type.

date_trunc() truncates (leaves seconds unchanged) - which is often what you really want:

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

小开

Discard milliseconds:

SELECT DATE_TRUNC('second', CURRENT_TIMESTAMP::timestamp);

2019-08-23 16:42:43

Discard seconds:

SELECT DATE_TRUNC('minute', CURRENT_TIMESTAMP::timestamp);

2019-08-23 16:42:00

小开

if you just want time, here is the code for postgres

SELECT DATE_TRUNC('second', CURRENT_TIMESTAMP::timestamp)::time;

it will return 'time without time zone' data type

小开

Try select date_trun('second',ur_date_field) as ur_date_field.

It will return results like 2022-06-21 12:56:41.