Select specific date from datetime field in SQLite

Question

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.

What is a register? Also please provide the desired output.

DDS
– DDS

2019-01-26 12:19:35 +00:00
Commented Jan 26, 2019 at 12:19 — DDS
– DDS, Commented Jan 26, 2019 at 12:19

Gordon Linoff · Accepted Answer · 2019-01-26 13:21:20Z

3

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'

answered Jan 26, 2019 at 13:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

forpas · Accepted Answer · 2019-01-26 12:38:08Z

2

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

edited Jan 26, 2019 at 12:38

answered Jan 26, 2019 at 12:19

forpas

165k10 gold badges51 silver badges85 bronze badges

Collectives™ on Stack Overflow

Select specific date from datetime field in SQLite

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related