How to convert mysql timestamp to date format

In mysql, you can use the FROM_UNIXTIME() function to convert the specified timestamp into date format. The function of this function is to convert the UNIX timestamp into a common format date and time value. The syntax "SELECT FROM_UNIXTIME(time Stamp,"format")".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, you can use the FROM_UNIXTIME() function to convert the specified timestamp into date format.

FROM_UNIXTIME() The function converts a UNIX timestamp into a common format date and time value. (The UNIX_TIMESTAMP () function is the inverse function of each other.)

Syntax:

select FROM_UNIXTIME(unix_timestamp,format)

format is an optional parameter and can be the following values:

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

• %W 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 day of the week name (Sun......Sat)

• %d month The number of days in the month, number (00……31)

• %e The number of days in the month, number (0……31)

• %m Month, number (01……12)

##%c Month, number (1……12)
%b Abbreviated month name (Jan...Dec)
%j Days in a year (001...366)
%H Hours (00... 23)
%k hours (0...23)
%h hours (01...12)
%I hours (01......12)
%l hours (1......12)
%i Minutes, 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 week(0……52), here Sunday is the first day of the week

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

• %% A literal "%".

• Example:

(1). Does not contain format parameter, return format:

SELECT FROM_UNIXTIME(addtime) FROM `web_game` ORDER BY ID desc LIMIT 5;

The return format is as follows:

2020-03-23 15:28:46
2020-03-23 15:16:43
2020-03-23 15:05:18
2020-03-23 15:01:01
2020-03-23 14:50:36

(2), containing format parameters:

SELECT FROM_UNIXTIME(addtime,'%Y-%m-%d') FROM `web_game` ORDER BY ID desc LIMIT 5;

The return format is as follows:

2020-03-23
2020-03-23
2020-03-23
2020-03-23
2020-03-23

[Related recommendations:

mysql video tutorial

]

The above is the detailed content of How to convert mysql timestamp to date format. For more information, please follow other related articles on the PHP Chinese website!