SQLite Series: Date & Time Functions


SQLite Series Part II: Functions for date and time!

SQLite is the most used database engine in the world but why is this even relevant? SQLite actually powers our data pipeline which enables you to do cool nifty things in our custom formulas! In this series, we will explore some of the most useful functions you can utilize in the pipeline and show off your SQL skills!

Part II uses SQLite Date & Time Functions. Other parts of the series will cover Core functions, aggregate functions, and JSON functions. Date & Time functions can be used with date, time, datetime, timestamp types and all the functions can be found here: SQLite Date & Time.

###Making dates look the way you want them to:

This requires the use of formatters. Since dates and time can be broken down to individual components like month, day, year, hour, min, etc… you can format your date/time to show what you want.

Date & Time formats in SQLite
These modifiers you can use in the pipeline. If you want to use modifiers in your query against your datasource, check your corresponding datasource SQL syntax to make sure you’re using the correct modifier associated with that datasource.

Format:	  Definition:				Example:
%d		  day of month		        01 - 31 (up to 31 days)
%f		  fractional seconds		11.111 (SS.SSS)			
%H		  hour					    00 - 24 (24 hours)			
%j		  day of year				01 - 366 (365 days)
%J		  Julian day number		    continuous count of days since the beginning of the Julian Period		
%m		  month					    01 -12 (Jan - Dec)		
%M		  minute					00 - 59 (60 min)
%s		  seconds 				    convert date/time value to seconds since 1970-01-01
%S		  seconds				    00-59 (60 seconds)
%w		  day of week				0-6 with Sunday==0	
%W		  week of year			    00-53 (ISO WEEKS)
%Y		  year					    0000-9999

With these modifiers, you can format your date & time to anything!

How to use modifiers:

You typically use modifiers in format functions. For SQLite, that is:

STRFTIME(format, date/time_column)

strftime(‘%Y-%m’%d’, ‘date_column’)

For the most common variations like date or datetime, you can use functions that will apply the format:

Function:		    Equivalent strftime():
date(...)		    strftime('%Y-%m-%d', ...)
time(...)		    strftime('%H:%M:%S', ...)
datetime(...)		strftime('%Y-%m-%d %H:%M:%S', ...)
julianday(...)		strftime('%J', ...)

###What else can you do with dates and times?

You can modify them and you can do math with them!

Modifiers can be appended to any of the functions date(), time(), datetime(), julianday()

NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years

The modifiers can be used to modify your date/time:

For additional math functions, Chartio has built in functions in the pipeline using SQLite:

dateadd( date_string, amount, 'unit' )
Adds a time interval to a date or datetime

datesub( date_string, amount, 'unit' )
Subtracts a time interval from a date or datetime

datediff( date_string1, date_string2, 'unit' (optional) )
Calculates the difference between two dates or datetimes. Formula will default to days if no unit is specified.

Units: day, week (datediff only), month, year, hour, minute, second

For more:

This was just a portion of all the functions you can use through SQLite! As we go through the series, we'll explore more functions you can utilize in your charts!