query with date field

softech
Here you go - 

community.YourCommunity.com/api/2.0/search?q=select * from messages where  post_time > 2019-07-01T00:00:00.000-00:00 and post_time < 2019-07-31T23:59:59.999-00:00

perfect and works!!

my problem earlier was i used quotes around the date 😞

thanks

thanks HariniVuppala 

and one more question, can i use max and min on my query?

like 

select max(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

I got an error using max(id) or min(id)

thanks

softech 
Max and min is not allowed. Can you please clarify what do you want to achieve by using max and min. 
If you want to get the maximum number of messages in single query then you have to use limit. By default it returns 25 results. 

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 limit 999 

For next batch, you have to pass the offset as below 
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 limit 999 offset 1

HI VikasB 

i am trying to get the first and  last message ID of each month , that's why I was hoping to use max and min

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 1

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 DESC limit 1