SQL Dates And Times — Opportunity Knocks

Relational databases are all about relationships, and keeping track of when things happen is at the core of good relationships with users; readers and customers. Accurately capturing and manipulating timestamps — the common term for a value with both a date and a time — the DATETIME data type and the EXTRACT() function are vital to robust, portable code.

DATETIME is a common datatype between various database implementations, each of which provides different (and incompatible) SQL data types and support functions. Start with DATETIME and choose another data type only if the costs outweigh the advantages — designing a solution that can’t be moved easily between databases is both frustrating and costly of time and effort.  

The topic of data storage and representation of time values is deep and rich, and there’s much more than one blog post can convey. Exploring some of the practical aspects of how SQL deals with time is within our scope.

What Time is it NOW()?

Most database implementations feature the NOW() function, or something similar like DATE(‘now’) and TIME(‘now’), or with different function names, like  CURDATE() and CURTIME(). These implementations return a text string with the current timestamp (the common term for a value with both a date and a time). This blog entry covers the common basics; check your particular database documentation for specifics.

SELECT NOW();

Displayed is the ISO 8601 timestamp representation for dates, which consists of:

• Year, month, and day, separated by dashes: YYYY-MM-DD
• The uppercase letter “T” delimiting the following time value
• Hours, minutes, seconds, and fractionals, separated by colons: HH:MM:SS:FF.FFFFFF
• A timezone designation, here a “Z” for “Zulu” (better known as Universal Coordinated Time)

EXTRACT()ing Timestamp Parts

The SQL EXTRACT() function returns a specified portion from a timestamp. Different database implementations may provide far greater time resolution than what’s shown below — down to the microsecond — but these are not portable across databases and should be used only to satisfy specific project requirements, like sub-second accuracy when dealing directly with hardware. Specific database documentation details all resolution options; following are the most common:

SELECT
EXTRACT( HOUR FROM NOW()),
EXTRACT( MINUTE FROM NOW()),
EXTRACT( SECOND FROM NOW()),
EXTRACT( YEAR FROM NOW()),
EXTRACT( MONTH FROM NOW()),
EXTRACT( DAY FROM NOW()),
EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW());

Manipulating Timestamps

Most database implementations provide some ability to manipulate a timestamp using the same units as shown above for EXTRACT(). DATE_ADD() returns a time that’s the product of the two arguments; DATE_SUB() returns a time that’s the second time quantity subtracted from the first time quantity.

As it’s used here, INTERVAL is a shorthand that becomes the total number of units to be added; think INTERVAL 1 DAY as being 60 seconds times 60 minutes times 24 hours.

SELECT
  NOW(),
  DATE_ADD(NOW(), INTERVAL 1 YEAR),
  DATE_SUB(NOW(), INTERVAL 1 YEAR);

Calculating Between Timestamps

Timestamps are used in calculations to find the difference between the current time and a time four days in the future, as implemented by DATEDIFF():

SELECT DATEDIFF(
NOW(),
DATE_ADD(NOW(), INTERVAL 4 DAY) ) AS 'elapsed time';

Or a time that’s two months and four days in the future:

SELECT DATEDIFF(
  NOW(),
  DATE_ADD(
    DATE_ADD( NOW(), INTERVAL 4 DAY),
    INTERVAL 2 MONTH)) AS 'elapsed time' ;

Writing Dates And Times

The DATE_FORMAT() function formats SQL dates using the same format markers as the C-language strtime() function, in the documentation of which are the commonly used format strings:

• %a — Abbreviated weekday (Sun-Sat)
• %b — Abbreviated month (Jan-Dec)
• %c — Numeric month (0-12)
• %D — Day of month with English suffix (0th, 1st, 2nd, 3rd)
• %d — Numeric day of month, zero-padded (00-31)
• %e — Numeric day of month (0-31)
• %H — Hour (00-23)
• %i — Minutes (00-59)
• %j — Day of year (001-366)
• %k — Hour (0-23)
• %l — Hour (1-12)
• %M — Month (January-December)
• %p — AM or PM
• %r — Time, 12-hour (hh:mm:ss followed by AM or PM)
• %S — Seconds (00-59)
• %T — Time, 24-hour (hh:mm:ss)
• %W — Weekday (Sunday-Saturday)
• %Y — Year, numeric, four digits (2022)
• %y — Year, numeric, two digits (22)

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %T') AS 'sortable date' ;

Best Practices, Warnings, Caveats

The errors that arise when using timestamps are due to implementation peculiarities and failures of modeling.

Before embarking on anything more complex than a prototype ensure that an understanding of the role that timestamps will play in the database model. Are dates of importance, or are times needed? Will times ever span more than one timezone? What operations need to be done with the time information? Will this data be exported to another database type?

Once written, timestamp-centric code is difficult to change.It is well worth the time (pun unintended) to whiteboard the intended uses for the timestamp data and then decide upon an appropriate data type that supports the SQL comparisons needed to implement the required operations.

Following are some of the issues which database timestamp users may encounter.

Creating Timestamps

Inserting a timestamp requires a format without timezone specified (at least in the implementations I’m using today); whether it will be stored as Universal Coordinated Time or with the local timezone depends upon the specific configuration; check to be sure. In this example, DATETIME values are inserted without timezone information but show as UTC when extracted:

CREATE TABLE t ( dt DATETIME );
INSERT into t VALUES ( '1999-12-21 23:59:59' );
SELECT * FROM t AS timestamps;

Arithmetically Comparing Timestamps

Before and after comparisons are fraught with peril, as some databases only check the date portions (meaning that timestamps with different times on the same day will be considered equivalent to certain comparisons). To verify that your database evaluates both date and time in comparisons:

CREATE TABLE t ( dt DATETIME );
INSERT into t VALUES
  ( '1999-12-21 23:59:59' ),
  ( '1998-12-21 23:59:59' ),
  ( '1997-12-21 23:59:59' ) ;

SELECT * from t WHERE dt = '1998-12-21 23:59:59' ;

SELECT * from t WHERE dt > '1998-12-21 23:59:59' ;

SELECT * from t WHERE dt < '1998-12-21 23:59:59' ;

Alternate solutions include storing separate DATE and TIME columns and using a nested SELECT statement to compare dates first and then process the times, depending on need.

NOW() Changes All The Time

The NOW() function should  be used to capture the moment of a transaction, and stored as a reference time. Using NOW() repeatedly in a block of SQL code will cause unintended consequences as code takes time to process, and NOW() at the start of your program will not be the same NOW() value a few lines of code later.

Conclusion

Covered here were some of the aspects of database considerations around times and dates, including

• storing
• extracting specific portions
• formatting
• arithmetically manipulating and comparing
• general best practices and caveats around choosing proper data types and operations to create robust, portable SQL timestamp code

Start Learning

To learn more about SQL, check out other SQL blog posts and enroll in our SQL Nanodegree program.

SQL Sample Table Code

The following will build and populate the sample data referred to in this blog post. Test SQL interactively at  SQL Fiddle or ExtendsClass.

(For this blog entry all SQL code is included in-line.)