CODEWITHSUNDEEP
sqlamazon-redshift
Michi
Michi

Reputation: 5471

Add 1 month to date and also display correct time from timestamp

I run this query on redshift to add 1 momth to a specific timestamp that:

SELECT
DATEADD(month, 1, date '2021-08-12 18:37:19') AS date_interval;

The result is this:

2021-09-12 00:00:00.0

How do I need to modify the query so the result also comes with the correct time looking like this:

2021-09-12 18:37:19

Upvotes: 1

Views: 221

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

If you want to do algebra on a timestamp, then you should start with a literal timestamp, not a date:

SELECT
    DATEADD(month, 1, '2021-08-12 18:37:19') AS date_interval;

The problem with your current query is that the following is a date literal:

date '2021-08-12 18:37:19'

Hence, the time component of your timestamp will be "zeroed" out to midnight before you even add one month.

Upvotes: 1

Related Questions