I’m trying to get a timestamp of the end-of-month for the latest/max timestamp currently in a table. My approach was to dateadd() one month to the timestamp, then use date_trunc() month on the result, then dateadd() again to subtract one second. Maybe there’s a better way? Maybe just setting the day of the month to days_in_month() and setting the time to 23:59:59 would be better??
My current query has two steps as I couldn't find another way of passing the result to the dateadd function.
select dateadd('s', -1, date_trunc('month', dateadd('M', 1, max))) from
(
select max from (select max(timestamp) from sensors)
)
You can accelerate quite a bit finding the latest timestamp in a table with two options:
(note: all the queries in the post can be executed live at https://demo.questdb.io)
a. Use meta tables to get the latest timestamp:
select max(maxTimestamp) from table_partitions(‘trades’);
b. Use LIMIT -1 to get the most recent row only:
select timestamp from trades limit -1;
I believe option b) should be faster. In any case, both should be pretty fast and way faster than scanning your table.
Now, there is the problem of finding the end of the month. Using the timestamp_ceil function you can find the ceiling per time unit, but that will give you the 1st microsecond of the next month, rather than the last microsecond of current month. If you need to have the latest microsecond, I believe your option to substract 1 microsecond (rather than 1 second) would be best.
select dateadd('u', -1, timestamp_ceil('M', timestamp)) from trades limit -1;
It is September 26 2024 11:08 UTC right now, and the result for that query is 2024-09-30T23:59:59.999999Z