Date functions on cursors

Community
1

Hello,

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??

However, what’s preventing me from doing this is trying call a date function on the result of a query.

select dateadd('M', 1, select max(timestamp) from sensors)

results in an error:

unexpected argument for function: dateadd. expected args: (CHAR,INT,TIMESTAMP). actual args: (CHAR constant,INT constant,CURSOR)

How do I get the CURSOR (presumably the result of the max() query) into something that can be consumed by the dateadd() function? I’ve tried converting to a string, but that didn’t help.

Thanks.

2

I think I figured it out:

select dateadd('M', 1, max) from
(
  select max from (select max(timestamp) from sensors)
)

I am still curious, though, if this is the best way to get the end of the current month of the max(timestamp) :

select dateadd('s', -1, date_trunc('month', dateadd('M', 1, max))) from
(
  select max from (select max(timestamp) from sensors)
)
3

You can accelerate quite a bit finding the latest timestamp in a table with two options:

(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