Title: Dates And Times
Slug: dates_and_times Summary: Dates and times in SQL. Date: 2016-05-01 12:00
Category: SQL
Tags: Basics
Authors: Chris Albon

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.



In [1]:

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

Get Current Date



In [2]:

    
%%sql

-- Select the current date
SELECT date('now');









    Out[2]:





    
        date('now')
    
    
        2017-01-19

Get Current Date And Time



In [3]:

    
%%sql

-- Select the unix time code '1200762133'
SELECT datetime('now', 'unixepoch');









    Out[3]:





    
        datetime('now', 'unixepoch')
    
    
        1970-01-29 10:42:53

Compute A UNIX timestamp into a date and time



In [4]:

    
%%sql

-- Select the unix time code '1169229733'
SELECT datetime(1169229733, 'unixepoch');









    Out[4]:





    
        datetime(1169229733, 'unixepoch')
    
    
        2007-01-19 18:02:13

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



In [5]:

    
%%sql

-- Select the unix time code '1171904533' and convert to the machine's local timezone
SELECT datetime(1171904533, 'unixepoch', 'localtime');









    Out[5]:





    
        datetime(1171904533, 'unixepoch', 'localtime')
    
    
        2007-02-19 10:02:13

Compute The Day Of The Week



In [6]:

    
%%sql

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









    Out[6]:





    
        strftime('%w','now')
    
    
        4