SQLite DateTime Comparison

Question

I cannot get reliable query results from the sqlite database using the datetime string as a comparison:

select * from table_1 where mydate >= '1/1/2009' and mydate <= '5/5/2009' 

how do i handle datetime comparison with sqlite?

update: mydate field is a DateTime data type

Decision:

following the datetime function and having a string format in the form YYYY-MM-DD HH: mm: ss I achieved good results as follows

 select * from table_1 where mydate >= Datetime('2009-11-13 00:00:00') and mydate <= Datetime('2009-11-15 00:00:00') 

Answer 1

SQLite does not have dedicated datetime types , but has several datetime functions . Follow the string presentation formats (in fact, only 1-10 formats), which are understood by these functions (storing the value as a string), and then you can use them, plus lexicographic string comparisons will correspond to date and time comparisons (if you do not use try compare dates with time or time with time, which in any case does not make much sense).

Depending on which language you use, you may even get an automatic conversion . (This does not apply to comparisons in SQL statements, as an example, but will make your life easier.)

Answer 2

To solve this problem, I save the dates as YYYYMMDD . Thus, where mydate >= '20090101' and mydate <= '20050505'

It just WORKS all the time. You may only need to write a parser to handle how users can enter their dates so that you can convert them to YYYYMMDD .

Answer 3

I had the same problem recently and solved it like this:

 SELECT * FROM table WHERE strftime('%s', date) BETWEEN strftime('%s', start_date) AND strftime('%s', end_date) 

Answer 4

The following works fine for me using SQLite:

 SELECT * FROM ingresosgastos WHERE fecharegistro BETWEEN "2010-01-01" AND "2013-01-01" 

Answer 5

Sqlite cannot be compared by date. we need to convert to seconds and apply it as a whole.

Example

 SELECT * FROM Table WHERE CAST(strftime('%s', date_field) AS integer) <=CAST(strftime('%s', '2015-01-01') AS integer) ; 

Answer 6

They worked after me.

 SELECT * FROM table_log WHERE DATE(start_time) <= '2017-01-09' AND DATE(start_time) >= '2016-12-21' 

Answer 7

Below are methods for comparing dates, but before that we need to determine the format of the date stored in DB

I have dates stored in the format MM / DD / YYYY HH: MM, so it needs to be compared in this format

• The query below compares the conversion of the date to the MM / DD / YYY format and the receipt of data from the last five days to today. The BETWEEN statement will help, and you can simply specify the start date and end date.

   select * from myTable where myColumn BETWEEN strftime('%m/%d/%Y %H:%M', datetime('now','localtime'), '-5 day') AND strftime('%m/%d/%Y %H:%M',datetime('now','localtime')); 

• Below the query will be used more than operator (>).

    select * from myTable where myColumn > strftime('%m/%d/%Y %H:%M', datetime('now','localtime'), '-5 day'); 

All the calculations that I have done use the current time, you can change the format and date to suit your needs.

Hope this helps you

Summved

Answer 8

You can also write your own user functions for processing dates in the format of your choice. SQLite has a pretty easy way to write your own custom functions. For example, I wrote several to add a length of time together.

Answer 9

I needed to save time with information about the time zone in it and was able to receive requests that work in the following format:

 "SELECT * FROM events WHERE datetime(date_added) BETWEEN datetime('2015-03-06 20:11:00 -04:00') AND datetime('2015-03-06 20:13:00 -04:00')" 

The time is stored in the database as a regular TEXT in the following format:

 2015-03-06 20:12:15 -04:00 

Answer 10

I made my request as follows:

 SELECT COUNT(carSold) FROM cars_sales_tbl WHERE date BETWEEN '2015-04-01' AND '2015-04-30' AND carType = "Hybrid" 

I got the @ifredy hint. All I did was want this request to run on iOS using Objective-C. And it works!

Hopefully someone who is developing iOS will also use this answer!

Answer 11

I have a situation where I want to receive data up to two days ago and until the end of today. I came to the next.

 WHERE dateTimeRecorded between date('now', 'start of day','-2 days') and date('now', 'start of day', '+1 day') 

Well, technically, I will also be at midnight tomorrow, like the original poster, if there were any data, but my data is all historical.

The key thing to keep in mind is the original poster deleted all data after 2009-11-15 00:00:00. Thus, any data recorded at midnight on the 15th was included , but there was no data after midnight on the 15th. If their request was,

 select * from table_1 where mydate between Datetime('2009-11-13 00:00:00') and Datetime('2009-11-15 23:59:59') 

Use of clarification for clarity.

It would be a little better. It still doesn’t take into account seconds of a jump in which an hour can have more than 60 seconds, but is good enough for discussion here :)