Forum Discussion

softech's avatar
softech
Guide
7 months ago

query with date field

trying to find out posts between two dates in "message"

 

i have something like 

https://mycommunity/api/2.0/search?q=SELECT%20post_time,%20post_time_friendly%20FROM%20messages%20WHERE%20ipost_time BETWEEN TWO dates

so, the query is pretty much 

SELECT post_time, post_time_friendly FROM messages Where [post_time between two date]

i am open to use either post_time or post_time_friendly

But my question is how to fit in the 2 dates. 

I tried different ways but none works

anyone can help?

thanks

  • HariniVuppala's avatar
    HariniVuppala
    7 months ago

    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

  • HariniVuppala's avatar
    HariniVuppala
    Expert
    7 months ago

    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

    • softech's avatar
      softech
      Guide
      7 months ago

      perfect and works!!

       

      my problem earlier was i used quotes around the date 😞

       

      thanks

       

    • softech's avatar
      softech
      Guide
      7 months ago

      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

       

      • VikasB's avatar
        VikasB
        Boss
        7 months ago

        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

    • softech's avatar
      softech
      Guide
      7 months ago

       

       

      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?

      • VikasB's avatar
        VikasB
        Boss
        7 months ago

        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




  • VikasB's avatar
    VikasB
    Boss
    7 months ago

    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