What are the mysql date format conversion functions?

mysql date format conversion functions are: 1. [SEC_TO_TIME(seconds)] converts seconds into time; 2. [TIME_TO_SEC(time)] converts time into seconds; 3. [ADDTIME(time, times)] Add times to time.

[Related learning recommendations: mysql tutorial (video)]

mysql date format conversion functions are:

1. Get the current system date

SELECT CURDATE() 
SELECT CURRENT_DATE()

-> 2016-01-16
-> 2016-01-16

2. Get the current system time

SELECT CURTIME() 
SELECT CURRENT_TIME()

-> 17:44:22
-> 17:44:22

3. NOW(), SYSDATE(), CURRENT_TIMESTAMP(), LOCALTIME(): Get the current date and time of the system

SELECT NOW() 
SELECT SYSDATE() 
SELECT CURRENT_TIMESTAMP() 
SELECT CURRENT_TIMESTAMP 
SELECT LOCALTIME() 
SELECT LOCALTIME

-> 2016-01-16 17:44:41
-> 2016-01-16 17:44:41
-> 2016-01-16 17:44:41
-> 2016-01-16 17:44:41
-> 2016-01-16 17:44:41
-> 2016-01-16 17:44:41

4, UNIX_TIMESTAMP (date): Get the timestamp

SELECT UNIX_TIMESTAMP() 
SELECT UNIX_TIMESTAMP(‘2016-01-16') 
SELECT UNIX_TIMESTAMP(‘2016-01-16 23:59:59')

-> 1452937627
-> 1452873600
-> 1452959999

5, FROM_UNIXTIME( unix_timestamp,format): Convert timestamp into date and time

SELECT FROM_UNIXTIME(1452959999) 
SELECT FROM_UNIXTIME(1452959999,'%Y-%m-%d %H:%i:%s')

-> 2016-01-16 23:59:59
-> 2016-01-16 23:59:59

6, SEC_TO_TIME(seconds): Convert seconds into time

SELECT SEC_TO_TIME(2378)

-> 00:39:38

7, TIME_TO_SEC(time): Convert time into Seconds

SELECT TIME_TO_SEC(‘22:23:00')

-> 2378

8. ADDTIME(time, times): Add times to time

SELECT ADDTIME(“2015-12-31 23:59:59”,'01:01:01')

-> 2016-01-01 01:01:00

9. CONVERT_TZ(date,from_tz,to_tz): Convert time zone

SELECT CONVERT_TZ(‘2004-01-01 12:00:00','+00:00','+10:00')

-> 2004-01-01 22:00:00

10. STR_TO_DATE(date, format): Convert a string into a date and time in format format

SELECT STR_TO_DATE(‘2015-01-01', ‘%Y-%m-%d')

-> 2015-01-01

11. LAST_DAY(date): Get the date of the last day of the month in date

SELECT LAST_DAY(SYSDATE()) 
SELECT LAST_DAY(‘2015-02-02') 
SELECT LAST_DAY(‘2015-02-02 00:22:33')

-> 2016-01-31
-> 2015-02-28
-> 2015-02-28

12. MAKEDATE(year,dayofyear): Get the date based on the parameters (year, day)

SELECT MAKEDATE(2015 ,32)

-> 2015-02-01

13. MAKETIME(hour, minute, second): Get the time based on the parameters (hour, minute, second)

SELECT MAKETIME(12 ,23 ,34 )

-> 12:23:34

14. YEARWEEK(date): Get the year and week of the date

SELECT YEARWEEK(SYSDATE()) 
SELECT YEARWEEK(‘2015-01-10') 
SELECT YEARWEEK(‘2015-01-10',1)

-> 201602
-> 201501
-> 201502

15. WEEKOFYEAR(date): Get the week of the current year

SELECT WEEKOFYEAR(SYSDATE()) 
SELECT WEEKOFYEAR(‘2015-01-10')

-> 2
-> 2

mysql Several commonly used time format conversion functions are summarized as follows

1, from_unixtime(timestamp, format):

timestamp is int Type time, such as 14290450779; format is the converted format, including the following formats:

• %M Month name (January...December)

• %W Day of the week name (Sunday...Saturday)

• %D Day of the month with English prefix (1st, 2nd, 3rd, etc. )

• ##%Y year, number, 4 digits

##%y year, number, 2 digits
%a Abbreviated name of the week (Sun......Sat)
%d Number of days in the month, number (00......31)
%e Number of days in the month, number (0...31)
%m Number of month, number (01...12)
%c Month, number (1...12)
%b Abbreviated month name (Jan...Dec)
% j Number of days in a year (001...366)
%H hours (00...23)
%k hours (0 ……23)
%h hours (01……12)
%I hours (01……12)
%l hour (1......12)
%i minute, number (00......59)
%r Time, 12 hours (hh:mm:ss [AP]M)
%T Time, 24 hours (hh:mm:ss)
%S seconds (00……59)
%s seconds (00……59)
%p AM or PM
%w The number of days in a week (0=Sunday...6=Saturday)

• ##%U The week (0… …52), where Sunday is the first day of the week

• %u week (0……52), where Monday is the first day of the week
2,

unix_timestamp(date)

: The function is exactly the opposite of from_unixtime(). The former converts the unix timestamp into a readable time. And unix_timestamp() converts the readable time into a unix timestamp, which is used when sorting the time stored in datetime. For example, unix_timestamp('2009-08-06 10:10:40'), you get 1249524739.

If unix_timestamp() does not pass parameters, call the now() function to automatically get the current time.

3,

date_format(date, format)

：date_format()

is to convert date or datetime type values For any time format. For example, in a common application scenario, a table has a field that is the update time and stores the datetime type. However, when displayed in the frontend, it only needs to display the year, month and day (xxxx-xx-xx). In this case, you can use date_format(date,'% Y-%m-%d ') processing without the need to use program loop processing in the result set.

If you want to know more about programming learning, please pay attention to the

php training

column!

The above is the detailed content of What are the mysql date format conversion functions?. For more information, please follow other related articles on the PHP Chinese website!