The world’s leading publication for data science, AI, and ML professionals.

5 Must-Know Date and Time Functions of SQL

Practical guide with examples

Photo by freestocks on Unsplash
Photo by freestocks on Unsplash

SQL is a programming language used by relational database management systems (RDBMS) to manage data in tabular form with labelled rows and columns.

We write SQL queries to retrieve data from relational databases. The queries can be as simple as retrieving all the entries from a table. However, it is not an ideal scenario.

SQL allows for writing advanced queries to filter and transform data. We can only retrieve the data we need in a desired format. It is far more efficient than retrieving all the data and then apply filtering and transformations.

SQL also provides many functions and methods to perform Data Analysis while getting the data from a database. In that sense, SQL can be considered as a data analysis tool as well.

In this article, we will cover 5 functions to be used on dates and times. I will be using MySQL as RDBMS. Although most of the SQL syntax is common among different RDBMSs, it is important to that there might be small differences.

I have created an Sql table that contains some data about the trending videos on YouTube in the US. It is a small part of a dataset available on Kaggle. The table is called "trending" and it contains the following data about trending videos in January, 2018.

Trending table (image by author)
Trending table (image by author)

1. Date function

The date function extracts the date part of a datetime expression. For instance, the publish_time column contains both date and time parts.

mysql> select publish_time from trending
    -> limit 3;
+---------------------+
| publish_time        |
+---------------------+
| 2018-01-01 15:30:03 |
| 2018-01-01 01:05:59 |
| 2018-01-01 14:21:14 |
+---------------------+

We can use the date function to extract the date part of the publish_time column.

mysql> select date(publish_time) from trending
    -> limit 3;
+--------------------+
| date(publish_time) |
+--------------------+
| 2018-01-01         |
| 2018-01-01         |
| 2018-01-01         |
+--------------------+

2. Time function

The time function is similar to the date function but it extracts the time part.

mysql> select time(publish_time) from trending
    -> limit 3;
+--------------------+
| time(publish_time) |
+--------------------+
| 15:30:03           |
| 01:05:59           |
| 14:21:14           |
+--------------------+

3. Datediff function

As its name suggests, the datediff function calculates the difference between two dates. The table contains the publish and trending dates for videos. The datediff function can be used to find the difference between them.

For instance, the following query returns the top 3 values of the differences between a video is published and it becomes trending.

mysql> select datediff(trending_date, date(publish_time)) as diff
    -> from trending
    -> order by diff desc
    -> limit 3;
+------+
| diff |
+------+
|   28 |
|   27 |
|   26 |
+------+

The datediff functions accepts two dates as its arguments. Since the publish_time column contains both date and time, we apply the date function to access its date part. The order by clause sorts the differences in descending order. Finally, we display the first three rows by setting the limit as 3.

We can also group the videos based on the difference between the publish and trending dates. The following query returns the top 3 differences between the publish and trending dates in terms of the number of videos.

mysql> select count(*) as qty,
    -> datediff(trending_date, date(publish_time)) as diff
    -> from trending
    -> group by diff
    -> order by qty desc
    -> limit 3;
+-----+------+
| qty | diff |
+-----+------+
| 833 |    3 |
| 823 |    4 |
| 790 |    5 |
+-----+------+

Most of the videos become trending in 3, 4, or 5 days after they are published.


4. Now and curdate functions

Both of them are used to access the current date. They come in handy to calculate how old an observation is. The now or curdate functions can be used with the datediff function as follows.

mysql> select avg(datediff(now(), date(publish_time)))
    -> as avg_time
    -> from trending;
+-----------+
| avg_time  |
+-----------+
| 1110.5513 |
+-----------+

We have calculated the how old these trending videos are in days on average. It is not quite useful in our case because we know all the videos in the table were published in January, 2018. However, it is important to know how to use these functions.


5. Weekday

There are many functions to access the parts of a date or time such as month name, week, weekday, year, and so on. I choose the weekday function as an example.

We can group the trending dates of the videos based on the weekday.

mysql> select count(*) as qty, weekday(trending_date) as wday
    -> from trending
    -> group by wday;
+-----+------+
| qty | wday |
+-----+------+
| 780 |    0 |
| 793 |    1 |
| 614 |    2 |
| 634 |    3 |
| 818 |    4 |
| 820 |    5 |
| 816 |    6 |
+-----+------+

The weekday function returns the weekday index. It seems like more videos become trending on the weekends than the week days.

Let’s do one more example. The weekofday function, as its name suggests, calculates the calendar week. We can apply it to the trending date as follows.

mysql> select count(*) qty, weekofyear(trending_date) as week_of_year
    -> from trending
    -> group by week_of_year;
+------+--------------+
| qty  | week_of_year |
+------+--------------+
|  494 |            1 |
|  949 |            2 |
| 1360 |            3 |
| 1352 |            4 |
| 1075 |            5 |
|   45 |            6 |
+------+--------------+

Conclusion

We have covered the basic date and time functions of SQL. You can see the entire list of such functions in MySQL documentation.

These functions are of great importance as time and date are two key factors in data analysis and manipulation.

Thank you for reading. Please let me know if you have any feedback.


Related Articles