CODEWITHSUNDEEP
pythonsqlitedatedatetime
badrobit
badrobit

Reputation: 793

SQLite greater than comparison return equal values as well.

I am using SQLite with python. I have a database with two fields (timestamp, reading).

the timestamp is an ISO8601 string formatted like this "YYYY-MM-DD HH:MM:SS.SSSSSS".

When I run this SQL query: 

SELECT timestamp, value FROM 'readings' WHERE timestamp > datetime('2017-08-30 14:19:28.684314')

I get all the appropriate readings where the timestamp is since the date provided but I also get the reading from the datetime I pass in (in the example: '2017-08-30 14:19:28.684314').

My question is why is the greater than comparison operator pretending it's a greater than or equal to operator?

Upvotes: 1

Views: 1913

Answers (2)

CL.
CL.

Reputation: 180020

SQLite does not have a separate data type for timestamps.

datetime() returns just a string in SQLite's default format:

> select datetime('2017-08-30 14:19:28.684314');
2017-08-30 14:19:28

This does not include milliseconds. So the comparison ends up between a string with milliseconds against a string without milliseconds; the first one is larger because (after the first 19 characters are equal) it has more characters.

Calling datetime() on both values removes the milliseconds from both values. It might be a better idea to call datetime() on neither value and to compare them directly.

Upvotes: 3

badrobit
badrobit

Reputation: 793

I solve the problem. I will detail it here in case it is helpful to someone else.

It was with my query. SQLite does not have a direct type for date's or datetime's.

My old query: 

SELECT timestamp, value FROM 'readings' WHERE timestamp > datetime('2017-08-30 14:19:28.684314')

was implicitly relying on SQL to figure out that the timestamp field was a datetime. SQLite stores them as TEXT fields internally.

When I modified my query to the following: 

SELECT timestamp, value FROM 'readings' WHERE datetime(timestamp) > datetime('2017-08-30 14:19:28.684314')

I started to get the results that I was expecting.

Upvotes: 0

Related Questions