获取一个月的最后一个时间戳 [英] Get the last timestamp of a month

问题描述

DB-Fiddle

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');

预期结果:

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            | 

---

在结果中，我想获取event_date每个月的最后一个时间戳.
因此，我尝试使用此查询:

---

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

查询已经给了我该月的最后一天.
但是，它代替 23:59:59 为其分配 00:00:00 .

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

我如何更改查询以使每个日期后面的 23:59:59 都与预期结果相同?

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

推荐答案

由于我无法访问任何redshift平台，所以不能肯定地说，但是下面的查询应该可以工作.请尝试这个.而不是减去一天减去一秒钟.

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

这篇关于获取一个月的最后一个时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！