datetime objects in SQlite3 and python

Question

Trying to store datetimes in my database project using python and sqlite3. Here's what I have so far...

import sqlite3

def connect():
    conn = sqlite3.connect("road_works.db")
    cur = conn.cursor()
    cur.execute("""CREATE TABLE IF NOT EXISTS road_works(id INTEGER PRIMARY 
        KEY, location TEXT, client TEXT, start_date TEXT, end_date TEXT)""")
    conn.commit()
    conn.close()


def insert(location, client, start_date, end_date):
    conn = sqlite3.connect("road_works.db")
    cur = conn.cursor()
    cur.execute("INSERT INTO road_works VALUES (NULL, ?, ?, ?, ?)",
               (location, client, start_date, end_date))
    conn.commit()
    conn.close()

insert("M25", "Lindor", "2019-03-16 20:00", "2019-07-16 06:00")

At the minute the database takes start_date and end_date as text. I want to change that to dates that I can then start doing calculations with like counting down the days/nights until the job is finished etc. The user will also be inputing the dates and times so I cant use datetime.datetime.now()/today() I have been reading into datetime but its more advanced than I'm used to if anyone could explain it better I would appreciate it.

I have been searching for the solution for a few days this is my first project it may be a little out of my league but I appreciate your attention.

Thanks :)

fixed! Thanks for the help!

def insert(location, client, start_date, end_date):
    conn = sqlite3.connect("road_works.db")
    cur = conn.cursor()
    cur.execute("INSERT INTO road_works VALUES (NULL, ?, ?, ?, ?)",
                (location, client, start_date, end_date))
    start_date = datetime.strptime(start_date, "%Y-%m-%d %H:%M")
    end_date = datetime.strptime(end_date, "%Y-%m-%d %H:%M")
    conn.commit()
    conn.close()

Roland Smith · Accepted Answer · 2019-01-03 14:03:48Z

2

There is no native datatype for dates in sqlite. The manual that I link to advises (see 2.2) three possible ways to store a date:

TEXT (ISO 8601 string)
REAL (Julian day numbers)
INTEGER (Unix Time)

My suggestion would be to use ISO 8601 format, since it is standardized and unambiguous. Additionally, as opposed to the alternatives it is human-readable without conversion. And datetime objects have standard conversion methods to and from a string for it.

See examples below:

In [1]: from datetime import datetime                                                                    

In [2]: now = datetime.now()                                                                             

In [3]: now                                                                                              
Out[3]: datetime.datetime(2019, 1, 3, 14, 53, 38, 596477)

In [4]: string = now.isoformat()                                                                         

In [5]: string                                                                                           
Out[5]: '2019-01-03T14:53:38.596477'

In [6]: datetime.fromisoformat(string)                                                                   
Out[6]: datetime.datetime(2019, 1, 3, 14, 53, 38, 596477)

edited Jan 3, 2019 at 14:03

answered Jan 3, 2019 at 13:55

Roland Smith

43.7k3 gold badges69 silver badges98 bronze badges

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

2 Comments

Michael.Hughes1 Over a year ago

Thanks for the response! I didnt know that I have been trying cur.execute('start_date DATETIME') :D. I will give your solution a try soon and let you know how I get on. Thanks again really appreciate the help.

Michael.Hughes1 Over a year ago

Hi I have put this in my code now. I got a 'ValueError: time data does not match format' when I tested if a normal string would work so it works.

Talha Junaid · Accepted Answer · 2019-01-03 13:45:25Z

0

You can use datetime.strtime to convert a string to datetime object

e.g.

from datetime import datetime
datetime_object = datetime.strptime('2019-03-16 20:00', '%Y-%m-%d %H:%M')

SO

answered Jan 3, 2019 at 13:45

Talha Junaid

2,40523 silver badges31 bronze badges

1 Comment

Michael.Hughes1 Over a year ago

Thanks for the response I will do more reading on that topic. Much appreciated.

Collectives™ on Stack Overflow

datetime objects in SQlite3 and python

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related