CODEWITHSUNDEEP
sqlamazon-redshift
Michi
Michi

Reputation: 5481

Get the last timestamp of a month

DB-Fiddle

CREATE TABLE PaL (
    id SERIAL PRIMARY KEY,
    event_date DATE
);

INSERT INTO PaL
(event_date)
VALUES 

('2020-01-01'),
('2020-02-05'),
('2020-03-20'),
('2020-04-15'),
('2020-05-11'),
('2020-06-18'),
('2020-07-19'),
('2020-12-31');

Expected Result:

event_date            |   last_timestamp_of_the_month        |
----------------------|---------------------------------- ---|-----------
2020-01-02            |       2020-01-31 23:59:59            |    
2020-02-05            |       2020-02-29 23:59:59            |   
2020-03-20            |       2020-03-31 23:59:59            |    
2020-04-15            |       2020-04-30 23:59:59            |
2020-05-11            |       2020-05-31 23:59:59            |    
2020-06-18            |       2020-06-30 23:59:59            |  
2020-07-19            |       2020-07-31 23:59:59            | 
2021-12-31            |       2020-12-31 23:59:59            |

In the results I want to get the last timestamp of each month of the event_date.
Therefore I tried to go with this query:

SELECT 
DATEADD(day, -1, DATEADD(month, +1, date_trunc('month', pl.event_date)))::timestamp AS last_timestamp_of_the_month
FROM PaL pl

The query already gives me the last day of the month.
However, instead of 23:59:59 it assigns 00:00:00 to it.

How do I need to change the query to get the 23:59:59 behind each date as in the expected results?

Upvotes: 1

Views: 274

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

Since I don't access to any redshift platform can't say for sure but below query should work. Please try this. Instead of subtracting a day subtract a second.

SELECT 
DATEADD(sec, -1, DATEADD(month, +1, date_trunc('month', pl.event_date)))::timestamp AS last_timestamp_of_the_month
FROM PaL pl

Upvotes: 1

Related Questions