Discard millisecond part from timestamp

sql postgresql casting timestamp rounding
Dipro Sen picture Dipro Sen · Apr 18, 2012 · Viewed 57.8k times · Source

How can I discard/round the millisecond part, better if the second part is also removed from a timestamp w/o timezone ?

Answer

Erwin Brandstetter picture Erwin Brandstetter · Apr 18, 2012

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);

Related questions

Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

I want to extract just the date part from a timestamp in PostgreSQL. I need it to be a postgresql DATE type so I can insert it into another table that expects a DATE value. For example, if I have 2011/05/26 09:00:00, …

Read More
How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?

In PostgreSQL I have a table with a varchar column. The data is supposed to be integers and I need it in integer type in a query. Some values are empty strings. The following: SELECT myfield::integer FROM mytable yields …

Read More
Subtracting 1 day from a timestamp date

I am using Datagrip for Postgresql. I have a table with a date field in timestamp format (ex: 2016-11-01 00:00:00). I want to be able to: apply a mathematical operator to subtract 1 day filter it based on a time window …

Read More