How to store and query dates in SQLite - using DB browser for Sqlite

St Tebb 0
Viewed 35

Not sure which data type and formatting to use when storing dates in my tables in DB browser for SQLite. Also not sure how to format the SQL queries when retrieving the data. For instance I've created a films database with an attribute for Release (as text data type), I want to be able to find films between two dates.

table : films +-------+------------+-------------+ | id | title | Release |
+-------+------------+-------------+ | 1 | Star Wars | 2000-01-01 |
| 2 | Star Trek | 2010-01-01 |

tried:

SELECT * FROM tblFilms WHERE Release BETWEEN (2000-01-01 AND 2010-01-01)

This does not return any values

date sqlite database
1 Answers

It's indiffernent if the following is from DB Browser for SQLite, or the SQLite terminal itself. The software is just a frontend.

Storing dates can be done in principle in any form, as SQLite is not typed, but natively SQLite works with one of these formats:

  • text in a subset of the ISO-8601 format: 2022-09-17 23:34:08
  • Julian day : 2459840.40688294
  • Seconds since (or before) 1970-01-01 00:00:00 UTC (the unix timestamp). : 1663451396

See documentation for SQLite dates.

Queries on dates, and limiting results, is explained with examples here.

Formatting results can be done using strftime:

select strftime("%m/%d/%Y", "2022-09-17") as 'US DATE'

returns 09/17/2022

MyICQ0
Related