CODEWITHSUNDEEP
sqlsqlitedatetime
M.E.
M.E.

Reputation: 5527

Select specific date from datetime field in SQLite

I have the following records in a table, column 3 is a Datetime type in sqlite.

sqlite> .schema candlestick
CREATE TABLE "Candlestick" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "timeframe" TEXT NOT NULL,
  "timestamp" DATETIME NOT NULL,
  "open" DECIMAL(12, 6) NOT NULL,
  "high" DECIMAL(12, 6) NOT NULL,
  "low" DECIMAL(12, 6) NOT NULL,
  "close" DECIMAL(12, 6) NOT NULL,
  "volume" DECIMAL(12, 6) NOT NULL
);

sqlite> select * from candlestick limit 10;
3668401|Europe/Berlin|1999-12-14 08:02:00+01:00|6183|6183|6183|6183|1
3668402|Europe/Berlin|1999-12-14 08:03:00+01:00|6180.5|6183|6180.5|6181|133
3668403|Europe/Berlin|1999-12-14 08:04:00+01:00|6181.5|6187.5|6181.5|6187.5|55
3668404|Europe/Berlin|1999-12-14 08:05:00+01:00|6186|6186|6184.5|6185|166
3668405|Europe/Berlin|1999-12-14 08:06:00+01:00|6182|6184.5|6180.5|6181|31
3668406|Europe/Berlin|1999-12-14 08:07:00+01:00|6181|6181|6175.5|6176|62
3668407|Europe/Berlin|1999-12-14 08:10:00+01:00|6174.5|6174.5|6174.5|6174.5|2
3668408|Europe/Berlin|1999-12-14 08:11:00+01:00|6176.5|6177|6176|6177|7
3668409|Europe/Berlin|1999-12-14 08:13:00+01:00|6174|6174|6174|6174|23
3668410|Europe/Berlin|1999-12-14 08:15:00+01:00|6174|6174|6173|6173|133

How would I select a given date (say 1999-12-15) from all registers? I.E. all registers that belong to that day, no matter what time they have.

Upvotes: 0

Views: 2195

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

A simple way is to use inequalities:

where timestamp >= '1999-12-15' and
      timestamp < '1999-12-16'

This formulation allows SQLite to use indexes to retrieve the value -- if an appropriate index is available.

SQLite also has a date() function (which is really strftime() the basis of all SQLite date/time functions). So you can also write:

where date(timestamp) = '1999-12-15'

Upvotes: 3

forpas
forpas

Reputation: 164214

The "timestamp" column's data type is actually TEXT in SQLite,
so you can use substr() function to extract only the date part:

select * from candlestick
where substr(timestamp, 1, 10) = '1999-12-15'

You can get the same result with the like operator:

select * from candlestick
where timestamp like '1999-12-15%'

See this link for more info on SQLite data types

Upvotes: 2

Related Questions