SQLite Forum

Query data between dates

Query data between dates

(1) By Victor Sued (visued) on 2021-02-05 11:11:31 [link] [source]


Im try make this query, bettween dates, and my select no return rows.

select * from Apontamento v where (v.DataInicio BETWEEN 'Wed Feb 03 2021 23:00:00 GMT-0300 (Brasilia Standard Time)' AND 'Thu Feb 04 2021 07:20:00 GMT-0300 (Brasilia Standard Time)')

2 lines between dates, exist in my database;

id OrdemServicoId OrdemServicoItemId ItemOS NegocioId Matricula MotivoParadaId DataInicio DataFim UsuarioId
390035272 1 26704 14 Wed Feb 03 2021 23:00:00 GMT-0300 (Brasilia Standard Time) Wed Feb 03 2021 23:58:38 GMT-0300 (Brasilia Standard Time) JOSE-SPIGOLON
974821305 1 26704 81 Wed Feb 03 2021 23:58:38 GMT-0300 (Brasilia Standard Time) JOSE-SPIGOLON

How to make this query correctly ?

(2) By Gunter Hick (gunter_hick) on 2021-02-05 12:09:45 in reply to 1 [link] [source]

Your choice of storage format does not lend itself to chronological comparisons. I suggest you either use a numeric datetime value or ISO date strings in UTC.

(3) By Victor Sued (visued) on 2021-02-05 12:22:51 in reply to 2 [link] [source]

it is not possible to solve without changing this format of the bd?

(5) By Richard Damon (RichardDamon) on 2021-02-05 17:58:58 in reply to 3 [source]

The key problem is that the format of the time stamp, as a text string in the format you are using, does not sort so that times between a given time would sort between those two other times. Note that your second time, since it begins with a T comes BEFORE your first time which begins with a W, as they are stored as just text strings.

You would need to make a computed column converting that value to something that IS monotonic in time, and preferably have an index on that column, to do your operations.

(4) By David Raymond (dvdraymond) on 2021-02-05 13:19:25 in reply to 1 [link] [source]

From the Quirks page

3.2. No Separate DATETIME Datatype

SQLite has no DATETIME datatype. Instead, dates and times can be stored in any of these ways:

  • As a TEXT string in the ISO-8601 format. Example: '2018-04-02 12:13:46'.
  • As an INTEGER number of seconds since 1970 (also known as "unix time").
  • As a REAL value that is the fractional Julian day number.

The built-in date and time functions of SQLite understand date/times in all of the formats above, and can freely change between them. Which format you use, is entirely up to your application.

Also see Date And Time Functions

(6) By Victor Sued (visued) on 2021-02-05 18:04:56 in reply to 4 [link] [source]

Thanks, i to go change format with is saved in db.