CODEWITHSUNDEEP
sqlite
Keith M
Keith M

Reputation: 1249

SQLite SELECT LIKE query with forward slash

In my database I'm storing timestamps in a TIMESTAMP field, the table structure is: (It would be difficult to change it to an int storing something like a unix epoch instead at this point)

CREATE TABLE timer (
    [id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
    [topic] VARCHAR(60) NOT NULL,
    [desc] VARCHAR(500)  NULL,
    [project] INTEGER  NOT NULL,
    [start] TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    [end] TIMESTAMP NULL,
    [user] INTEGER  NOT NULL
)

Now I need to select rows where the start date is in a certain month. I thought I could just do something like:

SELECT * FROM timer WHERE start LIKE "%month/%/year%"

But it's not working. But if I just do something like:

SELECT * FROM timer WHERE start LIKE "%8%"

It runs and selects all the rows with a start date in August (as well as ones with 8 in the time hehe)

I tried narrowing the issue down to:

SELECT * FROM timer WHERE start LIKE "%/%"

And found that the query returns absolutely nothing if the like string contains a forward slash /. I tried escaping the slash but that didn't change anything.

Why is my query not working when I include forward slashes?

Here is some sample data from the table:

id  topic     desc        project  start                   end                     user
1    Topic 1   A Desc.     1        8/12/2016 11:34:09 PM   8/12/2016 11:34:15 PM   1
2    Topic 2   A 2ndDesc.  1        8/13/2016 12:55:44 AM   8/13/2016 12:55:49 AM   1

EDIT:

As requested, here is an INSERT:

INSERT INTO timer (`topic`, `project`, `desc`, `start`, `end`, `user`) VALUES ('My Topic', '1', 'This is a Desc', '2016-08-15 10:03:41', '2016-08-16 12:03:41', '1')

Upvotes: 1

Views: 1488

Answers (1)

cartant
cartant

Reputation: 58410

SQLite's TIMESTAMP type stores dates/times as ISO8601 strings (there is no fundamental TIMESTAMP type in SQLite) like this:

2016-08-14 21:00:42

Which does not contain a slash. The data is likely being reformatted when you are presenting it.

SQLite doesn't constrain data types, so it's possible that you could be inserting non-ISO8601 strings. It depends upon your SQLite library and upon your INSERT statements. However, your start column is defined as TIMESTAMP DEFAULT CURRENT_TIMESTAMP, so default values for start will be ISO8601 strings. If you don't have consistently formatted times, you won't be able to query the data.

The ISO8601 format is sortable, so the SELECT you want to perform can be made more efficient:

SELECT * FROM timer WHERE start >= :from AND start < :to

Note that single-digit months in :from and :to would need to be left-padded with a zero. And note that the month in :to does not have to be a valid month. This would work:

SELECT * FROM timer WHERE start >= '2015-12' AND start < '2015-13'

Upvotes: 1

Related Questions