CODEWITHSUNDEEP
sqlsqlite
bb1990
bb1990

Reputation: 19

SQLite calculating difference of date got incorrect result

I am having trouble calculating date difference in SQLite.
I've set the value type to timestamp when setting up the tables, but the calculation for date seems only apply to the first number of my date entry.
I've try to use to_date('01/01/2020', 'mm/dd/yyyy') but then it return error saying not support to_date. My code is below, any suggestion would be much appreciated.

CREATE TABLE customer_join 
(
id INT,
country_code VARCHAR(10),
country_descrip VARCHAR(255),
register_date TIMESTAMP,
customer_id INT,
PRIMARY KEY (id),
FOREIGN KEY (customer_id) REFERENCES customer(id)
);

CREATE TABLE customer_order 
(
id INT,
item_name VARCHAR(25),
item_description VARCHAR(255),
number FLOAT(24),
order_date TIMESTAMP,
customer_id        INT,
PRIMARY KEY (id),
FOREIGN KEY (customer_id) REFERENCES patient(id)
);

INSERT INTO customer_join
Values (1,  1,  'none', '1/22/2017',    100),
        (2, 1,  'none', '1/23/2017',    101),
        (3, 1,  'none', '1/24/2017',    102),
        (4, 1,  'none', '1/25/2017',    103),
        (5, 1,  'none', '1/26/2017',    104),
        (6, 2,  'none', '1/27/2017',    101),
        (7, 2,  'none', '1/28/2017',    106),
        (8, 1,  'none', '1/29/2017',    107);
INSERT INTO customer_order
Values (1, 'A', 'none', 1, '2/23/2020', 101),
        (2, 'B', 'none', 1, '3/11/2027', 100),
        (3, 'B, C, D', 'none', 1, '4/10/2023', 100),
        (4, 'B, C, E', 'none', 1, '4/11/2020', 100),
        (5, 'R', 'none',1, '4/12/2099', 102);

SELECT (order_date - register_date) TIME_TO_ORDER
    FROM customer_join cj
    INNER JOIN 
        (SELECT customer_id , MIN(order_date) order_date
            FROM customer_order 
            GROUP BY customer_id) co 
    ON cj.customer_id = co.customer_id;

The code gives me the result:

    TIME_TO_ORDER
    2
    1
    3
    1

Which is not I wanted. I was trying to figure out how long does it take for customers to place their first order. Any suggestions?

Upvotes: 1

Views: 106

Answers (1)

forpas
forpas

Reputation: 164139

First, you must change the format of the dates in both tables to YYYY-MM-DD, which is the only valid text date format for SQLite.
Then use the function julianday() to get the difference in days between the dates:

SELECT cj.customer_id, 
       julianday(co.order_date) - julianday(cj.register_date) TIME_TO_ORDER
FROM customer_join cj 
INNER JOIN (
  SELECT customer_id , MIN(order_date) order_date
  FROM customer_order 
  GROUP BY customer_id
) co ON cj.customer_id = co.customer_id;

See the demo.
Results:

customer_id | TIME_TO_ORDER
----------: | ------------:
        100 |          1175
        101 |          1126
        102 |         30028

Upvotes: 0

Related Questions