Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Remove TIMESTAMP precision from NOW() result in PostgreSQL?

Is there any way to remove the precision from the result of the NOW() function in PostgreSQL?

"2012-08-21 10:23:34.867502"

I'm looking for the format to be:

"2012-08-21 10:23:34"

I'm attempting to update a column of type "timestamp without time zone" with the following SQL:

UPDATE table SET column = now();

Thanks!

like image 939
littleK Avatar asked Aug 21 '12 18:08

littleK


People also ask

What does NOW () return in Postgres?

What is PostgreSQL Now Function? The Now() function is used to return the current date and time of the time zone (default or user-defined). Its return type is the timestamp with the time zone.

How do I subtract a timestamp in PostgreSQL?

To calculate the difference between the timestamps in PostgreSQL, simply subtract the start timestamp from the end timestamp. Here, it would be arrival - departure . The difference will be of the type interval , which means you'll see it in days, hours, minutes, and seconds.

What is current timestamp in PostgreSQL?

Explanation: The above example shows the time and timestamp of all three functions working is the same. The current timestamp is basically used as the default timestamp value of a column in PostgreSQL. The current timestamp and the current time are to deliver the values with the time zone in PostgreSQL.


2 Answers

UPDATE tbl SET col = DATE_TRUNC('second', NOW());

See the docs for DATE_TRUNC.

like image 198
pilcrow Avatar answered Oct 04 '22 17:10

pilcrow


Simple answer is to cast it to zero precision.

select now()::timestamptz(0);
like image 23
Scott Marlowe Avatar answered Oct 04 '22 18:10

Scott Marlowe