How to select sales from 12PM today to next day 5AM in sqlite

I have a requirement to select the daily sales from Present Day 12PM to Next Day 5AM is there any way to do this.

Example: 2018-11-08 12:00 To 2018-11-09 05:00 (when initiated on 2018-11-08)

I am able to retrieve daily sales before 23:59 but unable to do get the next day's sales up to 05:00.

I have Orders table that have Order_date datatype is TEXT and datetime format is YYYY-MM-DD HH:mm

1 answer

  • answered 2018-11-08 09:02 MikeT

    I believe the following could be used :-

    SELECT * FROM orders 
      WHERE order_date 
        BETWEEN (strftime('%Y-%m-%d','now')||' 12:00') 
        AND (strftime('%Y-%m-%d','now','+1 days')||' 05:00')
    ;
    

    Working Example

    DROP TABLE IF EXISTS orders;
    CREATE TABLE IF NOT EXISTS orders (order_date TEXT);
    INSERT INTO orders (order_date) VALUES
            (strftime('%Y-%m-%d','now','-1 days')||' 11:59'), -- before
            (strftime('%Y-%m-%d','now')||' 00:00'), -- before
            (strftime('%Y-%m-%d','now')||' 11:59'), -- before (just)
            (strftime('%Y-%m-%d','now')||' 12:00'), --*** included
            (strftime('%Y-%m-%d','now')||' 23:59'), --*** included
            (strftime('%Y-%m-%d','now','+1 days')||' 05:00'), --**** included
            (strftime('%Y-%m-%d','now','+1 days')||' 05:01') -- after (just)
        ;
    
    SELECT * FROM orders 
     WHERE order_date 
       BETWEEN (strftime('%Y-%m-%d','now')||' 12:00') 
       AND (strftime('%Y-%m-%d','now','+1 days')||' 05:00')
    ;
    
    • Those marked with *** are rows (3 of them) that should be selected. enter image description here