Shulou(Shulou.com)06/01 Report--

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. The previous hour

Select date_sub (now (), interval 1 hour)

5. The next hour

Select date_sub (now (), interval-1 hour)

6. The first 30 minutes

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

7. The last 30 minutes

Select date_add (now (), interval 30 minute)

Day of acquisition:

SELECT curdate ()

Mysql > SELECT curdate ()

+-+

| | curdate () |

+-+

| | 2013-07-29 | |

+-+

Get the current date:

Mysql > select now ()

+-+

| | now () |

+-+

| | 2013-07-29 22:10:40 |

+-+

The day before:

Mysql > select date_sub (curdate (), interval 1 day)

+-+

| | date_sub (curdate (), interval 1 day) | |

+-+

| | 2013-07-28 | |

+-+

The day before the time of the day in parentheses, if you count the previous days, change the'1' in parentheses to the corresponding number of days. If you want to count the month or year, just change day to month or year.

Obtain the year of the previous day:

Mysql > SELECT YEAR (DATE_SUB (CURDATE (), INTERVAL 1 DAY))

+-+

| | YEAR (DATE_SUB (CURDATE (), INTERVAL 1 DAY)) |

+-+

| | 2013 |

+-+

An example of the date_sub () function:

Today is May 20, 2013.

Date_sub ('2012-05-25 minute interval 1 day) means 2012-05-24

Date_sub ('2012-05-25 minute interval 0 day) means 2012-05-25

Date_sub ('2012-05-25 minute interval-1 day) means 2012-05-26

Date_sub ('2012-05-31 minute interval-1 day) means 2012-06-01

Date_sub (curdate (), interval 1 day) stands for 2013-05-19

Date_sub (curdate (), interval-1 day) stands for 2013-05-21

Date_sub (curdate (), interval 1 month) stands for 2013-04-20

Date_sub (curdate (), interval-1 month) stands for 2013-06-20

Date_sub (curdate (), interval 1 year) stands for 2012-05-20

Date_sub (curdate (), interval-1 year) stands for 2014-05-20

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

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

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

MySQL gets the current date-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 gets the current date and time, there are the following functions in MySQL:

Current_timestamp ()

, current_timestamp

, localtime ()

, localtime

, localtimestamp-(v4.0.6)

, localtimestamp ()-- (v4.0.6)

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

1.2 get the current date + time (date + time) function: sysdate ()

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

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 | |

+-+

You can see that although the sleep is 3 seconds midway, the time value of the now () function is the same twice; the time value obtained by the sysdate () function differs by 3 seconds. This is how sysdate () is described in MySQL Manual: Return the time at which the function executes.

The sysdate () date-time function is rarely used in general.

two。 Get current date (date) function: curdate ()

Mysql > select curdate ()

+-+

| | curdate () |

+-+

| | 2008-08-08 | |

+-+

The following two date functions are equivalent to curdate ():

Current_date ()

, current_date

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

Mysql > select curtime ()

+-+

| | curtime () |

+-+

| | 22:41:30 |

+-+

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 eight time zone, so the local time = UTC time + 8 hours. UTC time is very useful when business involves multiple countries and regions.

Second, MySQL date and time Extract (select) function.

1. Select each part of the date time: date, time, year, quarter, month, day, hour, minute, second, microsecond

Set @ dt = '2008-09-100 07 purl 1530.123456'

Select date (@ dt);-- 2008-09-10

Select time (@ dt);-- 07Viru 1530.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-100 07 purl 1530.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);

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

Except for the functions of date () and time (), the MySQL Extract () function should have all other functions. It also has the function of selecting 'day_microsecond'' and so on. Note that not just day and microsecond are selected here, but all the way from the day section of the date to the microsecond section. How tough it is!

The only downside of the MySQL Extract () function is that you need to type the keyboard a few more times.

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

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

Set @ dt = '2008-08-08'

Select dayofweek (@ dt);-- 6

Select dayofmonth (@ dt);-- 8

Select dayofyear (@ dt);--

The date '2008-08-08' is the sixth day of the week (1 = Sunday, 2 = Monday,..., 7 = Saturday); the eighth day of January; 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, which can have two parameters, can be found in the manual. Weekofyear (), like week (), calculates the week in which "some day" is located. Weekofyear (@ dt) is equivalent to week (@ dt,3).

The MySQL weekday () function, like dayofweek (), returns the position of "someday" in the week. The difference lies in the reference standard, weekday: (0 = Monday, 1 = Tuesday,..., 6 = Sunday); dayofweek: (1 = Sunday, 2 = Monday,..., 7 = Saturday)

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

5. MySQL returns the week and month name functions: 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. The 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, I want to know how many days there are in the current month, which can be calculated like this:

Mysql > select now (), day (last_day (now () as days

+-+ +

| | now () | days | |

+-+ +

| | 2008-08-09 11:45:45 | 31 | |

+-+ +

Third, MySQL date and time calculation function

1. MySQL adds a time interval to the 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

The MySQL adddate (), addtime () function can be replaced by date_add (). Here is an example of how date_add () implements the addtime () function:

Mysql > set @ dt = '2008-08-09 12purl 1233'

Mysql >

Mysql > select date_add (@ dt, interval '01VO1VO1VERV 30' hour_second)

+-- +

| | date_add (@ dt, interval'01, interval'01, 15 hour_second) | |

+-- +

| | 2008-08-09 13:28:03 |

+-- +

Mysql > select date_add (@ dt, interval'1 01purl 1515 day_second)

+-- +

| | date_add (@ dt, interval'1 01v 1515 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. Suggestion: always use the date_add () date-time function instead of adddate (), addtime ().

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

Mysql > select date_sub ('1998-01-01 00 day_second, interval' 1 1)

+-- +

| | date_sub ('1998-01-01 00 day_second, interval' 1) |

+-- +

| | 1997-12-30 22:58:59 |

+-- +

The MySQL date_sub () date-time function is the same as date_add (), so I won't repeat it. In addition, there are two functions in MySQL, subdate () and subtime (), which are recommended to be replaced by date_sub ().

3. MySQL alternative date function: period_add (PMagne N), period_diff (P1MaginP2)

The function parameter "P" is formatted as "YYYYMM" or "YYMM", and the second parameter "N" adds or subtracts N month (month).

MySQL period_add (N): date plus / minus N month.

Mysql > select period_add (200808jue 2), period_add (20080808mai Murray 2)

+-+ +

| | period_add (200808jue 2) | period_add (20080808mai2) | |

+-+ +

| | 200810 | 20080806 | |

+-+ +

MySQL period_diff (P1Magol P2): date P1-P2, return 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 function: datediff (date1,date2), timediff (time1,time2)

MySQL datediff (date1,date2): subtracts date1-date2 from two dates to 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): subtracts time1-time2 from two dates and returns the time difference.

Select timediff ('2008-08-08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08) / (/ select timediff /-- 08:08:08

Select timediff ('08rig 08 08mm,' 001R 00R 00');-- 08:08:08

Note: the two argument types of the timediff (time1,time2) function must be the same.

4. MySQL date conversion function and time conversion function

1. MySQL (time, second) conversion function: time_to_sec (time), sec_to_time (seconds)

Select time_to_sec ('01VO 05');-- 3605

Select sec_to_time (3605);-- '01VRO 05'

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

Select to_days ('0000-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 conversion to date) function: str_to_date (str, format)

Select str_to_date ('08Compact 09Accord 2008,'% m _ swap% dmax% Y');-- 2008-08-09

Select str_to_date ('08Compact 09Compact 08','% mAccord% dash% y');-- 2008-08-09

Select str_to_date ('08.09.2008,'% m.% d.% Y');-- 2008-08-09

Select str_to_date ('08 09 30 minutes,'% HVA% iRO% s');-- 08:09:30

Select str_to_date ('08.09.2008 08 select str_to_date 09,'% m.%d.%Y% HV% iRO% s');-- 2008-08-09 08:09:30

As you can see, the str_to_date (str,format) conversion function converts some messy strings into date format. In addition, it can also be converted into time. "format" can be found in the MySQL manual.

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

Mysql > select date_format ('2008-08-08 22 2300mm,'% W% M% Y')

+-- +

| | date_format ('2008-08-08 22 2300,'% W% M% Y') |

+-- +

| | Friday August 2008 |

+-- +

Mysql > select date_format ('2008-08-08 22 23lane 01mm,'% Y% m% d% H% I% s')

+-- +

| | date_format ('2008-08-08 22 23lane 01mm,'% Y% m% d% H% I% s') |

+-- +

| | 20080808222301 | |

+-- +

Mysql > select time_format ('22 _ 22 _

+-+

| | time_format ('22 _ 22 _

+-+

| | 22.23.01 |

+-+

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

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

MySQL get_format () syntax:

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

All examples of the use of MySQL get_format ():

Select get_format (date,'usa');--'% m.d.% Y'

Select get_format (date,'jis');--'% Ymuri% mmury% d'

Select get_format (date,'iso');--'% Ymuri% mmury% 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% HRV% iRU% s'

Select get_format (datetime,'iso');--'% Y-%m-%d% HRV% iRU% 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');--'% HRV% iRO% s'

Select get_format (time,'iso');--'% HRV% iRO% s'

Select get_format (time,'eur');--'% H.I.% s'

Select get_format (time,'internal');--'% H% I% s'

The MySQL get_format () function uses fewer opportunities in practice.

6. MySQL piecing together date and time functions: makdedate (year,dayofyear), maketime (hour,minute,second)

Select makedate (2001-31);-- '2001-01-31'

Select makedate (2001-32);-- '2001-02-01'

Select maketime (12715-30);--'12-15-30'

5. MySQL timestamp (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);-- 1218169800

Select from_unixtime (1218290027);-- '2008-08-09 21 purl 53 purl 47'

Select from_unixtime (1218124800);-- '2008-08-08 00001

Select from_unixtime (1218169800);-- '2008-08-08 12 purl 3015'

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

3. MySQL timestamp (timestamp) conversion, increment and subtraction of functions:

Timestamp (date)-date to timestamp

Timestamp (dt,time)-- dt + time

Timestampadd (unit,interval,datetime_expr)-

Timestampdiff (unit,datetime_expr1,datetime_expr2)-

Take a look at the example section:

Select timestamp ('2008-08-08');-- 2008-08-08 00:00:00

Select timestamp ('2008-08-08 / 08 / 08 / 08 / 08 / 02 / 02 / 01 / 08 / 08 / 08 / 02 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01);-- 2008-08-08 09:01:01

Select timestamp ('2008-08-08 / 08 / 08 / 08 / 08 / 02 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 08 / 18 09:01:01)

Select timestampadd (day, 1, '2008-08-08-08);-- 2008-08-09 08:00:00

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

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

Select timestampdiff (year,'2002-05-01);-1

Select timestampdiff (day, 2002-05-01)

Select timestampdiff (hour,'2008-08-08 12);-12

Select datediff ('2008-08-08 12-0-00,' 2008-08-01-00);-- 7

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

6. MySQL time zone (timezone) conversion function

Convert_tz (dt,from_tz,to_tz)

Select convert_tz ('2008-08-08 12-00-0-8-12-00-0-0-8-0-0-8-12-0-0-8-12-0-0-8-0-0-0-0-8 04:00:00

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

Select date_add ('2008-08-08 12 interval-8 hour);-- 2008-08-08 04:00:00

Select date_sub ('2008-08-08 1200 hour, interval 8);-- 2008-08-08 04:00:00

Select timestampadd (hour,-8, '2008-08-08 12 0000');-- 2008-08-08 04:00:00

Convert by timestamp

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

There are two types of UNIX_TIMESTAMP functions in MySQL that can be called without parameters: UNIX_TIMESTAMP () returns a value: the difference in seconds from '1970-01-01 00 to the current time: SELECT UNIX_TIMESTAMP () = > 1339123415 2 calls with parameters: UNIX_TIMESTAMP (date) where date can be a DATE string and a DATETIME string A digital return value of TIMESTAMP or a local time in YYMMDD or YYYMMDD format: the difference between the number of seconds between the specified time and the specified time from '1970-01-01 00

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.