SQLite计算日期差异的结果不正确

Question

我在SQLite中计算日期差时遇到问题
在设置表时，我已将值类型设置为时间戳，但日期的计算似乎只适用于日期条目的第一个数字
我尝试使用to_date('01/01/2020'，'mm/dd/yyyy'(，但它返回错误，表示不支持to_date。我的代码如下，任何建议都将不胜感激。

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;

代码给了我结果：

TIME_TO_ORDER
2
1
3
1

这不是我想要的。我想弄清楚顾客下第一个订单需要多长时间。有什么建议吗？

Answer 1

首先，必须将两个表中的日期格式更改为YYYY-MM-DD，这是SQLite唯一有效的文本日期格式
然后使用函数julianday()获取日期之间的天数差：

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;

请参阅演示
结果：

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