CODEWITHSUNDEEP
postgresql
user1298426
user1298426

Reputation: 3717

How to subtract seconds from postgres datetime

Say I have column of type dateTime with value "2014-04-14 12:17:55.772" & I need to subtract seconds "2" seconds from it to get o/p like this "12:17:53".

Upvotes: 13

Views: 19922

Answers (2)

user330315
user330315

Reputation:

Postgres does not have a dateTime data type. I assume you mean a timestamp.

You can subtract an "interval" with the desired length from that column:

select the_timestamp_column - interval '2' second
from the_table

More about intervals in the manual

More about the operators available for date and timestamp columns in the manual

Upvotes: 6

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125524

select '2014-04-14 12:17:55.772'::timestamp - interval '2 seconds';

For greater flexibility it is possible to mutiply the interval

select '2014-04-14 12:17:55.772'::timestamp - 2 * interval '1 second';

If you want to truncate to the second

select date_trunc(
    'second', 
    '2014-04-14 12:17:55.772'::timestamp - interval '2 seconds'
);

Upvotes: 26

Related Questions