CODEWITHSUNDEEP
pythonsqlite
Driedan
Driedan

Reputation: 275

sqlite condition SELECT on datetime

I've made database in sqlite3(python) looking like this:

  [ 'LOCAL_TIME', 'PROPERTY A', 'PROPERTY B']
  [ 16-12-2013 00:00:00, 5.7, 8.0]
  [ 16-12-2013 00:20:00, 5.6,8.1]
  ...
  [16-12-2014 00:00:00,8.1,8.5]

Whith the following properties of the rows:

  'LOCAL_TIME' timestamp,
  'PROPERTY A' REAL,
  'PROPERTY B' REAL,
   PRIMARY KEY(LOCAL_TIME)

So for every 20 minutes their is a row and this for a whole year. I want to SELECT only those rows where the LOCAL_TIME is midnight (so 00:00:00 of every day).I don't know the solution and can't find it on the internet. I think something is wrong with the column LOCAL_TIME.

Thanks for helping me in advance

EDIT

I found a solution:

    import datetime as dt
    import sqlite3
    connect = sqlite3.connect(xxxxxxxx)
    cur=connect.cursor()
    time=[]
    PROPERTYA=[]
    PROPERTYB=[]
    for row in cur.execute('SELECT * FROM measurements'):
         temp=dt.datetime.strptime(row[0],'%Y-%m-%d %H:%M:%S')
         if (temp.hour==0 and temp.minute==0):
             time.append(row[0])
             PROPERTYA.append(row[1])
             PROPERTYB.append(row[2])

this works fine but I could better change the defenition of LOCAL_TIME to TEXT then.

Is their another solution ?

Upvotes: 1

Views: 920

Answers (1)

Vinod Sharma
Vinod Sharma

Reputation: 883

Store timestamp value as unixepoch, this makes comparison bit easy:

Have a look at the following Example:

Create Table:

create table measurements(id INTEGER PRIMARY KEY AUTOINCREMENT, LOCAL_TIME TIMESTAMP);

Insert Data:

insert into measurements(LOCAL_TIME) values(strftime('%s', '2013-12-16 00:00:00'));
insert into measurements(LOCAL_TIME) values(strftime('%s', '2013-12-16 00:20:00'));
insert into measurements(LOCAL_TIME) values(strftime('%s', '2013-12-17 00:00:00'));
insert into measurements(LOCAL_TIME) values(strftime('%s', '2013-12-17 00:20:00'));

Query "SELECT only those rows where the LOCAL_TIME is midnight (so 00:00:00 of every day)"

select datetime(LOCAL_TIME, 'unixepoch') from measurements where LOCAL_TIME - strftime("%s", date(LOCAL_TIME, 'unixepoch'))=0;

Simple idea: if datetime-date = 0 we select the row

Output:

2013-12-16 00:00:00
2013-12-17 00:00:00

Link for sqlite date,time functions: http://www.tutorialspoint.com/sqlite/sqlite_date_time.htm

Upvotes: 1

Related Questions