CODEWITHSUNDEEP
sqlpostgresqldatetimetimestamp
user5480949
user5480949

Reputation: 1668

Set minutes of timestamp to a specific value

What is the easiest way to set the exact minutes value of a timestamp?

This only adds minutes instead of setting the exact value:

SELECT timestamp_field + interval '2 minutes';

Upvotes: 2

Views: 208

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656596

Use date_trunc() before you add 2 minutes:

SELECT date_trunc('hour', timestamp_field) + interval '2 minutes';

Or, to retain seconds and sub-seconds:

SELECT date_trunc('hour', timestamp_field)
     + interval '2 min'
     + extract('seconds' FROM timestamp_field) * interval '1 sec';

Demo:

test=> SELECT now()
test-> UNION ALL
test-> SELECT date_trunc('hour', now())
test->      + interval '2 min'
test->      + extract('seconds' FROM now()) * interval '1 sec';
              now              
-------------------------------
 2021-03-23 03:59:57.373279+01
 2021-03-23 03:02:57.373279+01
(2 rows)

Should be substantially faster than manipulating the text representation and casting back.

Upvotes: 4

Related Questions