Dates And Times

Note: This tutorial was written using Catherine Devlin's SQL in Jupyter Notebooks library. If you have not using a Jupyter Notebook, you can ignore the two lines of code below and any line containing %%sql. Furthermore, This tutorial uses SQLite's flavor of SQL, your version might have some differences in syntax.

For more, check out Learning SQL by Alan Beaulieu.

# Ignore
%load_ext sql
%sql sqlite://
%config SqlMagic.feedback = False

Get Current Date

%%sql

-- Select the current date
SELECT date('now');
date('now')
2017-01-19

Get Current Date And Time

%%sql

-- Select the unix time code '1200762133'
SELECT datetime('now', 'unixepoch');
datetime('now', 'unixepoch')
1970-01-29 10:42:53

Compute A UNIX timestamp into a date and time

%%sql

-- Select the unix time code '1169229733'
SELECT datetime(1169229733, 'unixepoch');
datetime(1169229733, 'unixepoch')
2007-01-19 18:02:13

Compute A UNIX timestamp into a date and time and convert to the local timezone.

%%sql

-- Select the unix time code '1171904533' and convert to the machine's local timezone
SELECT datetime(1171904533, 'unixepoch', 'localtime');
datetime(1171904533, 'unixepoch', 'localtime')
2007-02-19 10:02:13

Compute The Day Of The Week

%%sql

-- Select the the day of this week (0 = Sunday, 4 = Thursday)
SELECT strftime('%w','now');
strftime('%w','now')
4