CODEWITHSUNDEEP
pythondatabasesqlite
user1430763
user1430763

Reputation: 37

How add column with time difference in Sqlite (python)

I want to update a table (sqlite database) adding column with constant value. However I am having some issues. I create table with

cursor.execute('''CREATE TABLE users(col1 text, col2 text, 
                dateCol datetime default current_timestamp,
                diff float)''')

Then I insert values into the table using 

cursor.execute('''INSERT INTO users (col1, col2, dateCol) 
                 VALUES (?, ?, ?)''', (n1_, n2_, n3_))

When I add constant values using following it works.

    cursor.execute('''UPDATE users SET 'diff' = 0.11 ''')

However, I want to insert time difference between dateCol with the newest record. How can I update my table adding difference in time (in seconds) with most recent (newest) record and all other records?

Upvotes: 0

Views: 417

Answers (1)

CL.
CL.

Reputation: 180030

SQLite has no date function to compute differences, but this is possible by using a numeric date format:

UPDATE users
SET diff =   julianday((SELECT MAX(dateCol) FROM users))
           - julianday(dateCol);

Upvotes: 1

Related Questions