Course Module 4. Working with databases - Lecture: Date Functions

3.1 List of functions for working with date and time

Date and time are one of the most common types of data stored in databases. That is why the list of functions for working with them is very large. Here are the most popular ones:

#	Function	Description
1	CURDATE()	Returns the current date
2	CURTIME()	Returns the current time
3	NOW(), LOCALTIME()	Returns the current date and current time

4	YEAR()	Returns year from date
5	MONTH()	Returns the month from a date
6	DAY(), DAYOFMONTH()	Returns the day from a date

7	HOUR()	Returns only hours from time
8	MINUTE()	Announces minutes from time
9	SECOND()	Returns seconds from time

10	DAYNAME()	Returns the name of the day of the week: Monday, ...
eleven	MONTHNAME()	Returns the name of the month: January, ...
12	WEEK()	Returns the week from a date
13	WEEKDAY()	Returns the number of the day of the week: Monday - 0, Tuesday - 1
14	WEEKOFYEAR()	Returns the week number of the year

15	DAYOFWEEK()	Returns the number of the day of the week: Sunday - 1, Monday - 2
16	DAYOFYEAR()	Returns the day of the year: 1-366
17	DATE()	Returns only the date from a "datetime" object

18	ADDDATE()	Adds days to a date
19	SUBDATE()	Subtracts days from a date
20	ADDTIME()	Adds time to time
21	SUBTIME()	Subtracts time from time

I deliberately grouped the functions into small groups to make it easier to understand how to work with them. Below we will consider one function from each group.

You can find a complete list of functions for working with date and time at the link: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

3.2 Calling functions

By the way, since we're learning so many functions, I think it's helpful to mention that the operator SELECTdoesn't have to be used with FROM. It can return the value of any expression. The general view of which is given by the template:

SELECT expression

And if you want to call some function, then you need to write code like:

SELECT function(options)

Below I will give some examples of the operation of the operator SELECTwithout recourse to tables:

#	Request	Result
1	SELECT 1+1	2
2	SELECT 13 MOD 5	3
3	SELECT RAND()	0.20771444235715497
4	SELECT CURDATE()	2022-06-04
5	SELECT CURTIME()	00:06:02
6	SELECT NOW()	2022-06-04 00:06:43

And, as you can see, in the table above, in order to get the current date and time, you just need to use one of the functions:

CURDATE()– current date;
CURTIME()– current time;
NOW()– current date and time.

3.3 Grouping data by year and month

Let's recall our task table with tasks for employees. Let's try to group tasks from this table by years. To do this, we use the function YEAR(), which returns the year from the date passed to it.

The first version of our query will look like this:

SELECT
    id,
    employee_id ,
    name,
    YEAR(deadline) AS year,
    deadline
FROM task

The result of this query will be:

id	employee_id	name	year	deadline
1	1	Fix a bug on the frontend	2022	2022-06-01
2	2	Fix a bug on the backend	2022	2022-06-15
3	5	Buy coffee	2022	2022-07-01
4	5	Buy coffee	2022	2022-08-01
5	5	Buy coffee	2022	2022-09-01
6	(NULL)	Clean up the office	(NULL)	(NULL)
7	4	Enjoy life	(NULL)	(NULL)
8	6	Enjoy life	(NULL)	(NULL)

We can see that all rows have the same year, so let's use two fields - year and month. The second version of our query will look like this:

SELECT
    id,
    employee_id ,
    name,
    YEAR(deadline) AS year,
    MONTH(deadline) AS month,
    deadline
FROM task

The result of this query will be:

id	employee_id	name	year	month	deadline
1	1	Fix a bug on the frontend	2022	6	2022-06-01
2	2	Fix a bug on the backend	2022	6	2022-06-15
3	5	Buy coffee	2022	7	2022-07-01
4	5	Buy coffee	2022	8	2022-08-01
5	5	Buy coffee	2022	9	2022-09-01
6	(NULL)	Clean up the office	(NULL)	(NULL)	(NULL)
7	4	Enjoy life	(NULL)	(NULL)	(NULL)
8	6	Enjoy life	(NULL)	(NULL)	(NULL)

I won’t tell you how to group tasks by year and month - you already studied this: use the operator GROUP BY.