mysql gets yesterday's date, today's date, tomorrow's date, and the time of the previous hour and the next hour

1. Current date

select DATE_SUB(curdate(),INTERVAL 0 DAY);

2. Tomorrow's date

select DATE_SUB(curdate(),INTERVAL -1 DAY);

3. Yesterday's date

select DATE_SUB(curdate(),INTERVAL 1 DAY);

4. Last hour

select date_sub(now(), interval 1 hour);

5. The next hour

select date_sub(now(), interval -1 hour);

6. First 30 minutes

select date_add(now(),interval -30 minute)

7. Last 30 minutes

select date_add(now(),interval 30 minute)

Acquired on the day:

SELECT curdate();

mysql> SELECT curdate();
+------------+
| curdate() |
+------------+
| 2013-07-29 |
+------------+

Get the current date:

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2013-07-29 22:10:40 |
+---------------------+

Get the previous day:

mysql> select date_sub(curdate(),interval 1 day);
+------------------------------------+
| date_sub(curdate(),interval 1 day) |
+------------------------------------+
| 2013-07-28 |
+------------------------------------+

The number in brackets is the day before today. If you want to count the previous few days, replace the '1' in brackets with the corresponding number of days. If you want to calculate the month or year, just change day to month or year

Get the year of the previous day:

mysql> SELECT YEAR(DATE_SUB(CURDATE(),INTERVAL 1 DAY));
+------------------------------------------+
| YEAR(DATE_SUB(CURDATE(),INTERVAL 1 DAY)) |
+------------------------------------------+
| 2013 |
+------------------------------------------+

Example of date_sub() function:

Today is May 20, 2013.

date_sub('2012-05-25',interval 1 day) means 2012-05-24
date_sub('2012-05-25',interval 0 day) means 2012-05-25
date_sub('2012-05-25',interval -1 day) means 2012-05-26
date_sub('2012-05-31',interval -1 day) means 2012-06-01
date_sub(curdate(),interval 1 day) means 2013-05-19
date_sub(curdate(),interval -1 day) means 2013-05-21
date_sub(curdate(),interval 1 month) means 2013-04-20
date_sub(curdate(),interval -1 month) means 2013-06-20
date_sub(curdate(),interval 1 year) means 2012-05-20
date_sub(curdate(),interval -1 year) means 2014-05-20


MySQL datediff(date1,date2): Subtract date1 - date2 from two dates and return the number of days.
select datediff('2008-08-08', '2008-08-01'); -- 7
select datediff('2008-08-01', '2008-08-08'); -- -7

1. MySQL gets the current date and time function

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

mysql> select now();

+---------------------+
| now() |
+---------------------+
| 2008-08-08 22:20:46 |
+---------------------+

In addition to the now() function that can obtain the current date and time, MySQL also has the following functions:

current_timestamp()
,current_timestamp
,localtime()
,localtime
,localtimestamp -- (v4.0.6)
,localtimestamp() -- (v4.0.6)

These date and time functions are equivalent to now(). Since the now() function is short and easy to remember, it is recommended to always use now() instead of the functions listed above.

1.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:

mysql> select now(), sleep(3), now();

+---------------------+----------+---------------------+
| now() | sleep(3) | now() |
+---------------------+----------+---------------------+
| 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 |
+---------------------+----------+---------------------+

mysql> select sysdate(), sleep(3), sysdate();

+---------------------+----------+---------------------+
| sysdate() | sleep(3) | sysdate() |
+---------------------+----------+---------------------+
| 2008-08-08 22:28:41 | 0 | 2008-08-08 22:28:44 |
+---------------------+----------+---------------------+

It can be seen that although the sleep period is 3 seconds, the time values ​​obtained by the now() function are the same; the time values ​​obtained by the sysdate() function differ by 3 seconds. The MySQL Manual describes sysdate() as follows: Return the time at which the function executes.

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

2. Get the current date (date) function: curdate()

mysql> select curdate();

+------------+
| curdate() |
+------------+
| 2008-08-08 |
+------------+

Among them, the following two date functions are equivalent to curdate():

current_date()
,current_date

3. Get the current time (time) function: curtime()

mysql> select curtime();

+-----------+
| curtime() |
+-----------+
| 22:41:30 |
+-----------+

Among them, the following two time functions are equivalent to curtime():

current_time()
,current_time

4. Get the current UTC date and time functions: utc_date(), utc_time(), utc_timestamp()

mysql> select utc_timestamp(), utc_date(), utc_time(), now()

+---------------------+------------+------------+---------------------+
| utc_timestamp() | utc_date() | utc_time() | now() |
+---------------------+------------+------------+---------------------+
| 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11 | 2008-08-08 22:47:11 |
+---------------------+------------+------------+---------------------+

Because our country is located in the East 8 time zone, the local time = UTC time + 8 hours. UTC time is very useful when the business involves multiple countries and regions.

2. MySQL date and time Extract function.

1. Select various parts of the date and time: date, time, year, quarter, month, day, hour, minute, second, microsecond

set @dt = '2008-09-10 07:15:30.123456';

select date(@dt); -- 2008-09-10
select time(@dt); -- 07:15:30.123456
select year(@dt); -- 2008
select quarter(@dt); -- 3
select month(@dt); -- 9
select week(@dt); -- 36
select day(@dt); -- 10
select hour(@dt); -- 7
select minute(@dt); -- 15
select second(@dt); -- 30
select microsecond(@dt); -- 123456

2. MySQL Extract() function, which can achieve similar functions as above:

set @dt = '2008-09-10 07:15:30.123456';

select extract(year from @dt); -- 2008
select extract(quarter from @dt); -- 3
select extract(month from @dt); -- 9
select extract(week from @dt); -- 36
select extract(day from @dt); -- 10
select extract(hour from @dt); -- 7
select extract(minute from @dt); -- 15
select extract(second from @dt); -- 30
select extract(microsecond from @dt); -- 123456

select extract(year_month from @dt); -- 200809
select extract(day_hour from @dt); -- 1007
select extract(day_minute from @dt); -- 100715
select extract(day_second from @dt); -- 10071530
select extract(day_microsecond from @dt); -- 10071530123456
select extract(hour_minute from @dt); -- 715
select extract(hour_second from @dt); -- 71530
select extract(hour_microsecond from @dt); -- 71530123456
select extract(minute_second from @dt); -- 1530
select extract(minute_microsecond from @dt); -- 1530123456
select extract(second_microsecond from @dt); -- 30123456

The MySQL Extract() function has all the functions except date() and time(). And also has the function of selecting 'day_microsecond', etc. Note that here we are not selecting just the day and microsecond, but selecting from the day part of the date all the way to the microsecond part. That’s powerful enough!

The only downside to the MySQL Extract() function is that it requires a few more keystrokes.

3. MySQL dayof... functions: dayofweek(), dayofmonth(), dayofyear()

Returns the position of the date parameter in the week, month, or year, respectively.

set @dt = '2008-08-08';

select dayofweek(@dt); -- 6
select dayofmonth(@dt); -- 8
select dayofyear(@dt); -- 221

The date '2008-08-08' is the 6th day of the week (1 = Sunday, 2 = Monday, ..., 7 = Saturday); the 8th day of the month; and the 221st day of the year.

4. MySQL week... functions: week(), weekofyear(), dayofweek(), weekday(), yearweek()

set @dt = '2008-08-08';

select week(@dt); -- 31
select week(@dt,3); -- 32
select weekofyear(@dt); -- 32

select dayofweek(@dt); -- 6
select weekday(@dt); -- 4

select yearweek(@dt); -- 200831

The MySQL week() function can have two parameters, see the manual for details. weekofyear() is the same as week(), both of which calculate the week number of a year that a certain day is in. weekofyear(@dt) is equivalent to week(@dt,3).

The MySQL weekday() function is similar to dayofweek(), both of which return the position of a certain day in the week. The difference lies in the reference standard, weekday: (0 = Monday, 1 = Tuesday, ..., 6 = Sunday); dayofweek: (1 = Sunday, 2 = Monday, ..., 7 = Saturday)

MySQL yearweek() function returns year(2008) + week position(31).

5. MySQL functions to return the name of the day of the week and month: dayname(), monthname()

set @dt = '2008-08-08';

select dayname(@dt); -- Friday
select monthname(@dt); -- August

Think about it, how to return the Chinese name?

6. MySQL last_day() function: returns the last day of the month.

select last_day('2008-02-01'); -- 2008-02-29
select last_day('2008-08-08'); -- 2008-08-31

The MySQL last_day() function is very useful. For example, if I want to know how many days there are in the current month, I can calculate it like this:

mysql> select now(), day(last_day(now())) as days;

+---------------------+------+
| now() | days |
+---------------------+------+
| 2008-08-09 11:45:45 | 31 |
+---------------------+------+

3. MySQL date and time calculation functions

1. MySQL adds a time interval to a date: date_add()

set @dt = now();

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

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

mysql> set @dt = '2008-08-09 12:12:33';

mysql>
mysql> select date_add(@dt, interval '01:15:30' hour_second);

+------------------------------------------------+
| date_add(@dt, interval '01:15:30' hour_second) |
+------------------------------------------------+
| 2008-08-09 13:28:03 |
+------------------------------------------------+

mysql> select date_add(@dt, interval '1 01:15:30' day_second);

+-------------------------------------------------+
| date_add(@dt, interval '1 01:15:30' day_second) |
+-------------------------------------------------+
| 2008-08-10 13:28:03 |
+-------------------------------------------------+

The date_add() function adds "1 hour 15 minutes 30 seconds" and "1 day 1 hour 15 minutes 30 seconds" to @dt respectively. Recommendation: Always use date_add() datetime function instead of adddate(), addtime().

2. MySQL subtracts a time interval from a date: date_sub()

mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);

+----------------------------------------------------------------+
| date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) |
+----------------------------------------------------------------+
| 1997-12-30 22:58:59 |
+----------------------------------------------------------------+

The MySQL date_sub() date and time function has the same usage as date_add(), so I will not go into details here. In addition, there are two functions in MySQL: subdate() and subtime(). It is recommended to use date_sub() instead.

3. MySQL alternative date functions: period_add(P,N), period_diff(P1,P2)

The format of the function parameter "P" is "YYYYMM" or "YYMM", and the second parameter "N" means adding or subtracting N month.

MySQL period_add(P,N): Add/subtract N months to a date.

mysql> select period_add(200808,2), period_add(20080808,-2)

+----------------------+-------------------------+
| period_add(200808,2) | period_add(20080808,-2) |
+----------------------+-------------------------+
| 200810 | 20080806 |
+----------------------+-------------------------+

MySQL period_diff(P1,P2): Date P1-P2, returns N months.

mysql> select period_diff(200808, 200801);

+-----------------------------+
| period_diff(200808, 200801) |
+-----------------------------+
| 7 |
+-----------------------------+

In MySQL, these two date functions are rarely used.

4. MySQL date and time subtraction functions: datediff(date1,date2), timediff(time1,time2)

MySQL datediff(date1,date2): Subtract date1 - date2 from two dates and return the number of days.

select datediff('2008-08-08', '2008-08-01'); -- 7
select datediff('2008-08-01', '2008-08-08'); -- -7

MySQL timediff(time1, time2): Subtract time1 - time2 from two dates and return the time difference.

select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('08:08:08', '00:00:00'); -- 08:08:08

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

IV. MySQL date conversion function, time conversion function

1. 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'

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

select to_days('0000-00-00'); -- 0
select to_days('2008-08-08'); -- 733627

select from_days(0); -- '0000-00-00'
select from_days(733627); --'2008-08-08'

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

select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09
select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09
select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09
select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 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.

4. MySQL Date/Time to Str (date/time converted to string) functions: date_format(date,format), time_format(time,format)

mysql> select date_format('2008-08-08 22:23:00', '%W %M %Y');

+------------------------------------------------+
| date_format('2008-08-08 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Friday August 2008 |
+------------------------------------------------+

mysql> select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');

+----------------------------------------------------+
| date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') |
+----------------------------------------------------+
| 20080808222301 |
+----------------------------------------------------+

mysql> select time_format('22:23:01', '%H.%i.%s');

+-------------------------------------+
| time_format('22:23:01', '%H.%i.%s') |
+-------------------------------------+
| 22.23.01 |
+-------------------------------------+

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.

5. MySQL gets the country and region time format function: get_format()

MySQL get_format() syntax:

get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal'

Full example of MySQL get_format() usage:

select get_format(date,'usa') ; -- '%m.%d.%Y'
select get_format(date,'jis') ; -- '%Y-%m-%d'
select get_format(date,'iso') ; -- '%Y-%m-%d'
select get_format(date,'eur') ; -- '%d.%m.%Y'
select get_format(date,'internal') ; -- '%Y%m%d'
select get_format(datetime,'usa') ; -- '%Y-%m-%d %H.%i.%s'
select get_format(datetime,'jis') ; -- '%Y-%m-%d %H:%i:%s'
select get_format(datetime,'iso') ; -- '%Y-%m-%d %H:%i:%s'
select get_format(datetime,'eur') ; -- '%Y-%m-%d %H.%i.%s'
select get_format(datetime,'internal') ; -- '%Y%m%d%H%i%s'
select get_format(time,'usa') ; -- '%h:%i:%s %p'
select get_format(time,'jis') ; -- '%H:%i:%s'
select get_format(time,'iso') ; -- '%H:%i:%s'
select get_format(time,'eur') ; -- '%H.%i.%s'
select get_format(time,'internal') ; -- '%H%i%s'

The MySQL get_format() function is rarely used in practice.

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

select makedate(2001,31); -- '2001-01-31'
select makedate(2001,32); --'2001-02-01'

select maketime(12,15,30); -- '12:15:30'

5. MySQL Timestamp function

1. MySQL gets the current timestamp function: current_timestamp, current_timestamp()

mysql> select current_timestamp, current_timestamp();

+---------------------+---------------------+
| current_timestamp | current_timestamp() |
+---------------------+---------------------+
| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |
+---------------------+---------------------+

2. MySQL (Unix timestamp, date) conversion function:

unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)

Here is an example:

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'

3. MySQL 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) --

See the examples section:

select timestamp('2008-08-08'); -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01

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

MySQL timestampadd() function is similar to date_add().

select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1
select timestampdiff(day,'2002-05-01','2001-01-01'); -- -485
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12

select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7

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

6. MySQL time zone conversion function

convert_tz(dt,from_tz,to_tz)

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

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

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

Convert by timestamp

SELECT a FROM test WHERE( UNIX_TIMESTAMP(start_time)-3600*24) > UNIX_TIMESTAMP('$now')

There are two types of UNIX_TIMESTAMP functions in MySQL for calling 1 Call without parameters: UNIX_TIMESTAMP() Return value: The number of seconds from '1970-01-01 00:00:00' to the current time Example: SELECT UNIX_TIMESTAMP() => 1339123415 2 Call with parameters: UNIX_TIMESTAMP(date) Where date can be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the YYMMDD or YYYMMDD format of the local time Return value: The number of seconds from '1970-01-01 00:00:00' to the specified time

SELECT UNIX_TIMESTAMP(NOW()) => 1339123415 Note: The return value of NOW() is a DATETIME string format.