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 SELECT
doesn'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 SELECT
without 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
.