CODEWITHSUNDEEP
pythonsqlsqlitedatetime
Павел Иванов
Павел Иванов

Reputation: 1913

How to write date and time from string to Sqlite table?

I have date and time in one-string format "2016-03-28T20:23:46+0800". How to convert it to date-time format Sqlite3 supports in "datetime" domain? I'm using python to read/write data in database.

I know that it's possible to achive using python:

>>> str = "2016-03-28T20:23:46+0800"
>>> temp = str.split('T')
>>> temp[1] = temp[1].rstrip('+')
>>> temp[1]
'20:23:46+0800'
>>> temp[1] = temp[1].split('+')[0]
>>> result = " ".join(temp)
>>> result
'2016-03-28 20:23:46'

but maybe the given string has a convertible format?

Upvotes: 1

Views: 3373

Answers (3)

user2138149
user2138149

Reputation: 17404

I would recommend you take the approach of using the sqlite3 text format for datetimes, because these are human readable.

My first instinct was also to reach for integer, but I soon realized this was a terrible idea when I had no idea what the values were when inspecting the database with a DBMS. (PS: My favorite tool right now is DBeaver CE.)

I would suggest you take the slightly arduous approach of creating a string in ISO8601 format, and then stripping off the 3 floating point digits off the end which are a higher precision than sqlite3 specifies.

(For some reason the sqlite3 documentation doesn't exactly follow the ISO8601 standard, which should have 6 decimal digits of floating point precision for the sub-seconds component. Instead they appear to use 3.)

Here's a function which does that for you. You can strip out my debugging output if you wish. PS: It was late when I wrote this. If I messed something up please let me know. I haven't tested it for cases where the fractional subseconds part produces zeros for the final 3 digits. I don't know if these are present in the original string produced by strftime or not. I would assume that they are? Who knows. This implementation should cope either way, unless I messed something up.

import datetime

now = datetime.datetime.now()

now = now_to_sqlite3_iso8601(now)
print(now)

# helpful conversion function
def now_to_sqlite3_iso8601(now):
    iso8601 = now.strftime("%Y-%m-%d %H:%M:%S.%f")
    index = iso8601.find('.')
    
    if index > 0:
        ss = iso8601[index:]
        print(f'ss={ss}')
        length = len(ss)
        if length > 4: # include the '.' character
            len_to_remove = length - 4
            final_len = len(iso8601) - len_to_remove
            iso8601 = iso8601[0:final_len]
    else:
        print('warning \'.\' not found in string')
    
    iso8601 = iso8601
    print(f'iso8601: {iso8601}')
    return iso8601

Upvotes: -1

jfs
jfs

Reputation: 414855

sqlite3 won't produce an error until you try to select the value back as datetime object if you try to insert a timestamp in the wrong format. To validate the input date/time string, convert it to datetime.datetime first. sqlite3 module can convert datetime.datetime automatically:

#!/usr/bin/env python3
import sqlite3
from datetime import datetime

db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE dates (timestamp TIMESTAMP)')
db.executemany('INSERT INTO dates VALUES(?)', 
               [[datetime.utcnow()], ['2016-03-28 20:23:46.000000+0800']])
print(*db.execute('SELECT timestamp FROM dates'), sep='\n')

Output

('2016-05-14 05:08:52.487113',)
('2016-03-28 20:23:46.000000+0800',)

TIMESTAMP field accepts a string in ISO 8601 format 'YYYY-MM-DD HH:MM:SS.mmmmmm' or datetime.datetime object.

To return datetime.datetime() back, you could use detect_types=sqlite3.PARSE_DECLTYPES (if you need the timezone support; define your own converter that returns timezone-aware datetime objects).

Upvotes: 1

cheng chen
cheng chen

Reputation: 489

date time format does not exist in sqlite, one work around is to use number sequence as date time for example:

def Strtime2Num(result):
    date_object = datetime.strptime(Strtime,"%Y-%m-%dT%H:%M%S+0800")
    Strtime = date_object.strftime('%Y%m%d%H%M')
    return int(result)

when select in sql command, you can use 

con.execute(UPDATE YearUpdateCapa SET %s = %d WHERE DateTime >= %d AND DateTime <= %d' % (name, avail,start,end))

Upvotes: 1

Related Questions