Datetime
From charlesreid1
 |
O NOES!!!
|
Dealing with date and time stamps in CSV files for populating an SQL database, all using Python.
Contents
The First Question: Dealing With CSV Date/Time Stamps
The First Question: How to deal with date and time stamps from CSV files, and their many varieties?
We will be using Python to handle the date and time stamps.
Dates and Times in Python
You can use the excellent datetime utility to deal with all things dates and times.
Our task is to turn a string, like "2016-02-04 22:09:06", into a datetime object. This object will have an underlying representation, consisting of the year, the month, the day, the hour, the minute, and the second.
To turn a string into a datetime object, use the datetime.strptime() method. Here is a link to the datetime library documentation, about strptime(): https://docs.python.org/2/library/datetime.html#datetime.datetime.strptime
We can call it like so:
test_datetime.py
import datetime
x = "2016-02-04 22:09:06"
x_datetime = datetime.datetime.strptime(x, '%Y-%m-%d %H:%M:%S')
If we print out x_datetime, we can see it is a datetime object:
>>> print x_datetime 2016-02-04 22:09:06 >>> print type(x_datetime) <type 'datetime.datetime'> >>>
The Second Question: SQL and Date/Time Stamps
The Second Question: How does SQL deal with date and time stamps? How do you initialize a table with a column for date and time stamps, and how do you create a new record with that information?
Data Storage in Sqlite
All data stored by Sqlite has a storage class of null, integer, real (float), text, or blob.
Sqlite does not have a separate boolean storage class, booleans are stored as integer 0 (false) or integer 1 (true)
Sqlite does not have a storage class aside for storing dates and/or times. Built-in date and time functions of sqlite are capable of storing dates and times as TEXT, REAL, or INTEGER values
For example, I can store the date and time as a string "YYYY-MM-DD HH:MM:SS.SSS"
I can also store it as a real number: number of days since noon in Greenwich on November 24, 4714 BC, according to proleptic Gregorian calendar.
I can store it as an integer, using a Unix timestamp, which is the number of seconds since 1970-01-01 00:00:00 UTC.
Dates/Times in Sqlite
SQLite documentation: https://www.sqlite.org/lang_datefunc.html
SQLite supports 5 date and time functions (date(), time(), datetime(), julianday(), strftime())
The date and time functions use the ISO-8601 standard for their date and time formats. Dates are returned as YYYY-MM-DD and times are returned as HH:MM:SS.
The datetime function returns YYYY-MM-DD HH:MM:SS
Julian day returns number of days since noon in Greenwich on November 24, 4714 BC.
strftime() routine returns date formateed according to format string specified as first argument. This is a string with any of the following characters:
%d day of month: 00 %f fractional seconds: SS.SSS %H hour: 00-24 %j day of year: 001-366 %J Julian day number %m month: 01-12 %M minute: 00-59 %s seconds since 1970-01-01 %S seconds: 00-59 %w day of week 0-6 with Sunday==0 %W week of year: 00-53 %Y year: 0000-9999 %% %
Also, the output of each function can be reproduced by strftime():
date(...) strftime('%Y-%m-%d', ...)
time(...) strftime('%H:%M:%S', ...)
datetime(...) strftime('%Y-%m-%d %H:%M:%S', ...)
julianday(...) strftime('%J', ...)
Modifiers to the time string can alter date and time too.
Examples
Compute the current date:
SELECT date('now');
Compute the date and time given a unix timestamp 1092941466.
SELECT datetime(1092941466, 'unixepoch');
Compute last day of current month:
SELECT datetime(1092941466, 'unixepoch');
Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.