Xtykutl

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

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.