获取一个月的最后一个时间戳 [英] 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');
预期结果:
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屋!
查看全文