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:
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
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!