DATETIME is a SQL data type that deals with calendar values.

There is another datatype called DATE that’s very similar to DATETIME , with just one difference. DATETIME includes information about time (i.e. hours, minutes, and seconds of an entry), and DATE doesn’t.

On Interview Query, all date values are stored as DATETIME rather than DATE.

Luckily for us, most SQL functions, clauses, and keywords have intuitive ways of interacting with DATETIME values.

• The MAX and MIN aggregate functions will choose the latest and earliest date in the query, respectively.
• The > and < relationships become “before” and “after” relationships.
• ORDER BY will order value from earliest to most recent. Note that ORDER BY orders values in ascending order by default.

Arithmetic on DATETIME Values

Sadly, the above convenience does not extend to arithmetic operators.

To add or subtract any number of years, months, weeks, etc. from DATETIME values, we need to use the DATE_ADD and DATE_SUB functions. To use them, we must specify a column and a time interval to add or subtract.

For example, to get the date 4 days before a column called created_at, we can use the following query:

DATE_SUB(created_at, INTERVAL 4 DAY)

In general, the syntax for using these functions works as follows:

DATE_<operation>(<column>, INTERVAL <number> <unit>)`

Possible units include HOUR, DAY, MONTH, MINUTE, and SECOND.

The DATE_FORMAT Function

We can choose the formats in which we retrieve DATE_TIME values with the DATE_FORMAT function. For example:

SELECT DATE_FORMAT("2017-06-15", "%Y")

would only retrieve the year “2017”.

In general, we can use the DATE_FORMAT function by specifying a column name and the format we prefer:

SELECT DATE_FORMAT(<date_column>,<format>) 

The format must be a string that specifies the desired format. We use specifiers to define where and how to write each time unit. A full list of specifiers can be found here.

Apart from the specifiers, we can use the characters we need to format our time. For example, %m-%d-%Y will write the month number, the day number, and the year separated by dashes. In this case, the specifiers are %m, %d, and %Y.

JOINing on DATETIME Values via DATE_FORMAT

JOINing tables on DATETIME values could be tricky, precisely because they can be specific to the millisecond. Usually, we only care about matching up to units like the hour, day, or even month.

Transforming a DATETIME column via the DATE_FORMAT function can “remove” this unneeded information to allow for more flexible JOINs.