CODEWITHSUNDEEP
sqlitedatetime
rvpals
rvpals

Reputation: 189

SQLite: simple SQL - where in timestamp field

I have a table with timestamp field call EVENT_TS (so it's stored as GMT time stamp)

I want to a query that would retrieve the records based on EVENT_TS, and query only returns today's result.

Here is what record looks at:

EVENT_DESC | EVENT_TS
-----------+--------------------
event1     | 2011-11-14 11:22:00
event2     | 2011-11-11 12:22:00

SO if today is Nov 14, I want it to return only today's event, which is event1.

Upvotes: 0

Views: 370

Answers (3)

kla
kla

Reputation: 271

Assuming this is sql server:

select * from event
where convert(varchar(10),event_ts ,101) = convert(varchar(10),getdate(),101)

Upvotes: 0

mu is too short
mu is too short

Reputation: 434665

Something like this should do the trick (assuming that your system timezone is properly configured):

select *
from your_table
where date(event_ts, 'localtime') = date(current_timestamp, 'localtime')

Upvotes: 1

Larry Lustig
Larry Lustig

Reputation: 50970

Assuming that the type of that field is TEXT (SQLite can store date as TEXT, INTEGER, or REAL):

 SELECT * FROM Event WHERE Event_TS LIKE '2011-11-14%'

This query will be able to use and index on Event_TS if you've created one.

Upvotes: 0

Related Questions