softech To get the accurate results, you can use the following query.
select post_time, post_time_friendly from messages where post_time>=2024-04-01T00:00:00.000-00:00 and post_time <= 2024-05-01T23:59:59.999-00:00
one more question with time
while this work
select post_time, post_time_friendly from messages where post_time>=2024-04-01T00:00:00.000-00:00 and post_time <= 2024-05-01T23:59:59.999-00:00
select post_time, post_time_friendly,solution_data.time from messages where solution_data.time>=2024-04-01T00:00:00.000-00:00
basically I want to get the total accepted solution in a month, so, I want to just make an URL like this:
https://productioncommunity.publicmobile.ca/api/2.0/search?q=SELECT count(*) FROM messages WHERE is_solution=true and solution_data.time >= 2024-04-01T00:00:00.000-00:00 and solution_data.time <= 2024-05-01T23:59:59.999-00:00
and also if I use slect (some fieids) from messages, I can use ORDER BY post_time but ORDER BY solution_data.time does not work.
sorry, I am still not get the handle of these time/date fields, seems like solution_data.time is very tricky
but why this does not work?
softech
API does not support "solution_data.time" as a constraint(for Where clouse as well as for Order By).
If you only want to get the first and last message id for the month, you can use below queries -
select id from messages where post_time>=2024-04-01T00:00:00.000-00:00 and post_time <= 2024-05-01T23:59:59.999-00:00 Order by post_time ASC limit 1select id from messages where post_time>=2024-04-01T00:00:00.000-00:00 and post_time <= 2024-05-01T23:59:59.999-00:00 Order by post_time DESC limit 1
