CODEWITHSUNDEEP
sqlite
tonywang
tonywang

Reputation: 191

Unexpected result of select in sqlite

I am having trouble understanding why one of the columns from the SELECT results returns 0. I am trying to inner join Table 1 and 2. The column "Time2" has a similar query to Time3 in Table 1. This is the query I have:

SELECT [ID] AS [ID1],
       [RESULT.CurrentDate] AS [CurrentDate],
       strftime("%j", substr([RESULT.CurrentDate], 1, instr([RESULT.CurrentDate], ' ') - 1) ) AS Time3,
       b.Time2,
       [ID2],
       [No],
       Date,
       abs(b.Time2 - Time3) AS Time4
  FROM Table1 a
       INNER JOIN
       Table2 b ON a.[ID1] = b.[ID2] AND 
                abs(b.Time2 - Time3) < 7

Here are some of the results returned:

ID1     CurrentDate                    Time Time2  ID2          No    Date                Time4
001   2019-11-07 12:21:17           311 296   001   1817    2019-10-23 11:42:27 0
001   2019-11-07 12:21:17           311 311   001   2309    2019-11-07 17:07:13 0
002   2019-11-07 12:21:43           311 296   002   1808    2019-10-23 11:52:59 0
002   2019-11-07 12:21:43           311 311   002   2329    2019-11-07 17:19:16 0
003   2019-11-07 12:22:19           311 296   003   1835    2019-10-23 10:57:17 0
003   2019-11-07 12:22:19           311 311   003   2321    2019-11-07 17:29:48 0

I do not understand why (b.Time2 - Time3) is 0? I expected, it should be 15 and 0 per row. Is there something I missed?

Upvotes: 0

Views: 52

Answers (1)

forpas
forpas

Reputation: 164064

In SQLite there is no Datetime data type.
Any value like 2019-11-07 12:21:17 is considered as TEXT, so when you try to subtract, say:

'2019-11-07 12:21:17' - '2019-11-06 10:20:10'

what SQLite does is to try to convert each of the values to a numeric value starting from its 1st char and moving to the right until a non numeric char is reached.
So the result is:

2019 - 2019

and you get 0, because in both dates the valid numeric part starting from the left is 2019.
If you want to subtract dates in SQLite, use julianday():

julianday(b.Time2) - julianday(Time3)

which will return the difference in days (with a decimal part).
See the demo.

Upvotes: 1

Related Questions