Working with Date and Time Strings

You have already learned about manipulating text strings. Now, I will go through how to manipulate date and time strings. This presents some problems since there is a multitude of ways to format date and time strings and store them.

Learning Objectives:

• Describe the complexities of adjusting date and time strings.
• Discuss the different formats in which dates and times are presented.
• List and describe the 5 different functions in SQL that can be used to manipulate date and time strings.
• Use the ‘STRFTIME’ function.
• Compute the current date and use it to compare to a recorded date in your data.
• Use the NOW function.
• Combine several ‘date and time’ functions together to manipulate data.

Working with Date Variables.

If your data contains only a date, your queries will be simpler. In contrast, if they contain a time portion then it gets complicated.

Here are four examples of how date and time strings can be stored.

Wednesday, September 17th, 2008.
9/17/2008 5:14:56 P.M. EST
9/17/2009 19:14:56 GMT
2612008 (Julian format)

Date Formats.

Let’s look at a few formats here.

DATE
Format: YYYY-MM-DD

DATETIME
Format: YYYY-MM-DD HH:MI:SS

TIMESTAMP
Format: YYYY-MM-DD HH:MI:SS

If you query a DATETIME with:

WHERE PurchaseDate='2016-12-12'

You will get no results. Why? It is because we also have the timestamp included too.

SQLite Date Time Functions.

Here are some time functions that SQLite supports:

DATE(timestring, modifier, modifier, ...)
TIME(timestring, modifier, modifier, ...)
DATETIME(timestring, modifier, modifier, ...)
JULIANDAY(timestring, modifier, modifier, ...)
STRFTIME(format, timestring, modifier, modifier, ...)

Timestrings.

There is also ‘Julian Day’. Where time strings can be in several acceptable formats.

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DD MM
YYYY-MM-DD MM:SS
YYYY-MM-DD MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS

Here are some Date and Time String Examples.

SELECT Birthdate,
STRFTIME('%Y', Birthdate) AS Year,
STRFTIME('%M', Birthdate) AS Month,
STRFTIME('%D', Birthdate) AS Day
FROM employees

Doing this allows you to extract the year, month and day of the date string so that you can easily work with this data.

To compute the current date, you can use the following code:

SELECT DATE('now')

This might be used in analysis since you might want to check how much time has passed since a certain date or event.

You could also extract the year, month and date for the current date with a slightly modified version of the above code.

SELECT STRFTIME('%Y %m %d', 'now');

You could even do this for time too.

SELECT STRFTIME('%H %M %S %s', 'now');

Let's take an example where you know the birthdate of a user but need to extract their age from this. How could we achieve this? Well, we use the STRFTIME function as follows:

SELECT Birthdate,
DATE(('now') - Birthdate) AS Age
FROM employees

That concludes this post on Date and Time strings.

Do let me know what you think of this post. I am still a learner myself and I would love to hear your thoughts. You are more than welcome to message me on LinkedIn or Twitter.