Mysql date formatting and complex date range query

Preface
Query usage scenario cases

Time interval query
Query date today time comparison data
Common cycle time queries

mysql date time function

1 Get the current date + time (date + time) function: now()
2 Get the current date + time (date + time) function: sysdate()
3 Get the current timestamp function: current_timestamp, current_timestamp()
4 Get the current date (date) function: curdate()

mysql date time conversion function

1 Date time conversion string format
2 Convert string to date time
3 (Date, Day) Conversion Function
4 (time, seconds) conversion function
5. Piecing together date and time functions
6 Unix timestamp, date conversion functions

mysql date and time calculation function

1 Add a time interval to the date: date_add()
2 Subtract a time interval from a date: date_sub()
3 Date and time subtraction functions: datediff(date1,date2), timediff(time1,time2)
4 Timestamp conversion, increase and decrease functions:

Timezone conversion functions

convert_tz(dt,from_tz,to_tz) function
Time zone conversion can also be achieved through date_add, date_sub, timestampadd.

Preface

Recently, I have been working on a project involving complex date queries in MySQL. Date queries are actually quite common in database queries, such as querying the interval information from the start date to the end date, querying information where the date is less than the valid date, querying today's date, tomorrow's date, and making comparisons, etc.

Query usage scenario cases

Time interval query

Query, data from June 1, 2021 to August 25, 2021

SELECT
    *
FROM
    `dateTest` 
where DATE_FORMAT(date,'%Y%m%d') BETWEEN '20210601' and '20210825'

Including the start time, excluding the end time

However, the writing method of DATE_FORMAT(date,'%Y%m') cannot use indexes, and the query is extremely slow when the amount of data is large.

Query date today time comparison data

select * from t_user t where t.CREATE_TIME>=curdate()

Convert the date and time to time format through DATE

select * from t_user t where DATE (t.CREATE_TIME)>=DATE (now())

Common cycle time queries

-- Today select fullName,addedTime from t_user where to_days(addedTime) <= to_days(now());
-- yesterday select fullName,addedTime from t_user where to_days(NOW()) - TO_DAYS(addedTime) <= 1;
-- In the past 7 days select fullName,addedTime from t_user where date_sub(CURDATE(),INTERVAL 7 DAY) <= DATE(addedTime);
-- In the past 30 days SELECT fullName,addedTime FROM t_user where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(addedTime);
-- This month SELECT fullName,addedTime FROM t_user WHERE DATE_FORMAT( addedTime, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' );
-- Last month SELECT fullName,addedTime FROM t_user WHERE PERIOD_DIFF( date_format( now() , '%Y%m' ) , date_format( addedTime, '%Y%m' ) ) =1;
-- Query this quarter's data select fullName,addedTime FROM t_user where QUARTER(addedTime)=QUARTER(now());
-- Query the data of the previous quarter select fullName,addedTime FROM t_user where QUARTER(addedTime)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
-- Query this year's data select fullName,addedTime FROM t_user where YEAR(addedTime)=YEAR(NOW());
-- Query the data of the previous year select fullName,addedTime FROM t_user where year(addedTime)=year(date_sub(now(),interval 1 year));
-- Query data 6 months from now select fullName,addedTime FROM t_user where addedTime between date_sub(now(),interval 6 month) and now();

-- Query the data of the current week SELECT fullName, addedTime FROM t_user WHERE YEARWEEK(date_format(addedTime,'%Y-%m-%d')) = YEARWEEK(now());
-- Query last week's data SELECT fullName, addedTime FROM t_user WHERE YEARWEEK(date_format(addedTime,'%Y-%m-%d')) = YEARWEEK(now())-1;
-- Query last month's data select fullName,addedTime FROM t_user where date_format(addedTime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m');
-- Query the data of the current month select fullName, addedTime FROM t_user where DATE_FORMAT(addedTime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');
select fullName,addedTime FROM t_user where date_format(addedTime,'%Y-%m')=date_format(now(),'%Y-%m');

-- Query the data of the specified time period select fullName,addedTime FROM t_user where addedTime between '2017-1-1 00:00:00' and '2018-1-1 00:00:00';
select fullName,addedTime FROM t_user where addedTime >='2017-1-1 00:00:00' and addedTime < '2018-1-1 00:00:00';

mysql date time function

1 Get the current date + time (date + time) function: now()

select now();

+---------------------+
| now() |
+---------------------+
| 2021-08-31 16:16:32 |
+---------------------+

2 Get the current date + time (date + time) function: sysdate()

The sysdate() date and time function is similar to now(), except that now() gets its value at the beginning of execution, while sysdate() gets its value dynamically when the function is executed. Take a look at the following example to understand:

select now(), sleep(3), now();
+---------------------+----------+---------------------+
| now() | sleep(3) | now() |
+---------------------+----------+---------------------+
| 2021-08-31 16:20:12 | 0 | 2021-08-31 16:20:12 |
+---------------------+----------+---------------------+

We can see that the time before and after has not changed, so the value is obtained before the execution starts.

sysdate() Date and time function, generally rarely used.

3 Get the current timestamp function: current_timestamp, current_timestamp()

select current_timestamp, current_timestamp();

+---------------------+---------------------+
| current_timestamp | current_timestamp() |
+---------------------+---------------------+
| 2021-08-31 16:27:26 | 2021-08-31 16:27:26 |
+---------------------+---------------------+

4 Get the current date (date) function: curdate()

-- 2021-08-31
select curdate();

mysql date time conversion function

1 Date time conversion string format

Date/Time to Str (date/time converted to string) function: date_format(date,format), time_format(time,format)

select date_format('2021-08-31 22:23:01', '%Y%m%d%H%i%s');
+----------------------------------------------------+
| date_format('2021-08-31 22:23:01', '%Y%m%d%H%i%s') |
+----------------------------------------------------+
| 20210831222301 |
+----------------------------------------------------+

MySQL date and time conversion functions: date_format(date,format), time_format(time,format) can convert a date/time into various string formats. It is an inverse transformation of the str_to_date(str,format) function.

2 Convert string to date time

MySQL Str to Date (string to date) function: str_to_date(str, format)

select str_to_date('08/31/2021', '%m/%d/%Y'); -- 2021-08-31
select str_to_date('08/31/2021' , '%m/%d/%y'); -- 2021-08-31
select str_to_date('08.31.2021', '%m.%d.%Y'); -- 2021-08-31
select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
select str_to_date('08.09.2021 08:31:30', '%m.%d.%Y %h:%i:%s'); -- 2021-08-31 08:09:30

As you can see, the str_to_date(str,format) conversion function can convert some messy strings into date format. Alternatively, it can be converted to time. For "format", please refer to the MySQL manual.

3 (Date, Day) Conversion Function

MySQL (date, day) conversion functions: to_days(date), from_days(days)

select to_days('0000-00-00'); -- 0
select to_days('2021-08-31'); -- 738398

4 (time, seconds) conversion function

MySQL (time, seconds) conversion functions: time_to_sec(time), sec_to_time(seconds)

select time_to_sec('01:00:05'); -- 3605
select sec_to_time(3605); -- '01:00:05'

5. Piecing together date and time functions

MySQL makedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2021,31); -- '2021-01-31'
select makedate(2021,32); --'2021-02-01'
select maketime(12,15,30); -- '12:15:30'

makedate(2021,31) The year is in front and the day is behind. By default, it starts from January. If the number of days behind is a certain number, it will move to a new month.

6 Unix timestamp, date conversion functions

unix_timestamp(),

unix_timestamp(date),

from_unixtime(unix_timestamp),

from_unixtime(unix_timestamp,format)

select unix_timestamp(); -- 1218290027
select unix_timestamp('2008-08-08'); -- 1218124800
select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800

select from_unixtime(1218290027); --'2008-08-09 21:53:47'
select from_unixtime(1218124800); --'2008-08-08 00:00:00'
select from_unixtime(1218169800); --'2008-08-08 12:30:00'

select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'

mysql date and time calculation function

1 Add a time interval to the date: date_add()

set @dt = now(); -- define variables select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 hour); -- add 1 hour
select date_add(@dt, interval 1 minute); -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);

select date_add(@dt, interval -1 day); -- sub 1 day plus -1 day means minus 1 day

MySQL adddate(), addtime() functions can be replaced by date_add(). The following is an example of date_add() implementing the addtime() function:

set @dt = '2021-08-31 12:12:33';
select date_add(@dt, interval '01:15:30' hour_second); -- 2021-08-31 13:28:03
select date_add(@dt, interval '1 01:15:30' day_second); -- 2021-09-01 13:28:03

2 Subtract a time interval from a date: date_sub()

-- 2021-08-29 22:58:59
select date_sub('2021-08-31 00:00:00', interval '1 1:1:1' day_second);

The date_sub() date and time function has the same usage as date_add(), so I will not go into details here.

3 Date and time subtraction functions: datediff(date1,date2), timediff(time1,time2)

-- Subtract date1 - date2 from two dates and return the number of days.
select datediff('2021-08-31', '2021-08-30'); -- 1
select datediff('2021-08-31', '2021-08-20'); -- -11
-- Subtract time1 - time2 from two dates and return the time difference.
select timediff('2021-08-31 08:08:08', '2021-08-30 00:00:00'); -- 32:08:08
select timediff('08:08:08', '00:00:00'); -- -08:08:08

The two parameters of the timediff(time1, time2) function must be of the same type.

4 Timestamp conversion, increase and decrease functions:

timestamp(date) -- date to timestamp

timestamp(dt,time) -- dt + time

timestampadd(unit,interval,datetime_expr) --

timestampdiff(unit,datetime_expr1,datetime_expr2) --

select timestamp('2021-08-31'); -- 2021-08-31 00:00:00
select timestamp('2021-08-31 08:00:00', '01:01:01'); -- 2021-08-31 09:01:01
select timestamp('2021-08-31 08:00:00', '10 01:01:01'); -- 2021-09-10 09:01:01

select timestampadd(day, 1, '2021-08-31 08:00:00'); -- 2021-09-01 08:00:00
select date_add('2021-08-31 08:00:00', interval 1 day); -- 2021-09-01 08:00:00

-- The MySQL timestampadd() function is similar to date_add().
select timestampdiff(year,'2021-08-31','2001-01-01'); -- -20
select timestampdiff(day,'2021-08-31','2001-01-01'); -- --7547
select timestampdiff(hour,'2021-08-31 12:00:00','2021-08-31 00:00:00'); -- -12

select datediff('2021-08-31 12:00:00', '2021-08-31 00:00:00'); -- 0

MySQL timestampdiff() function is much more powerful than datediff(), which can only calculate the number of days between two dates.

Timezone conversion functions

convert_tz(dt,from_tz,to_tz) function

-- 2021-08-31 04:00:00
select convert_tz('2021-08-31 12:00:00', '+08:00', '+00:00');

Time zone conversion can also be achieved through date_add, date_sub, timestampadd.

select date_add('2021-08-31 12:00:00', interval -8 hour); -- 2021-08-31 04:00:00
select date_sub('2021-08-31 12:00:00', interval 8 hour); -- 2021-08-31 04:00:00
select timestampadd(hour, -8, '2021-08-31 12:00:00'); -- 2021-08-31 04:00:00

This is the end of this article about MySQL date formatting and complex date range query. For more information about MySQL date formatting and date range query, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

Detailed explanation of single-row function code of date type in MySQL
MySQL method of generating random numbers, strings, dates, verification codes and UUIDs
The pitfall of MySQL numeric type auto-increment
Analysis of MySQL's problem with sorting numbers in strings
MySQL example to explain single-row functions and character math date process control

<<: JS ES new features: Introduction to extension operators

>>: Use of align-content in flex layout line break space