Mysql converts unix timestamp to date format, and MySQL queries the current time

This article introduces two tips: how mysql converts a timestamp to a common time format and how MySql queries the current database time.

(1) run the following command to query the current time of a database in MySql:

Select now (); // return format: 11:44:42

(2) mysql converts unix timestamps to common time formats

The time field is stored in the database. The general storage type is int. What should we do when we want to see the specific time of the current data? Visually, we can only see a pile of numbers. If we need to convert it into a date format that humans can understand, besides using program processing, mySQL also provides a function that converts a unix timestamp to a date-time string type. For example, if the stored time is 1340422884, it needs to be converted to "year-month-day: minute: seconds.

Use the FROM_UNIXTIME function provided by MySQL. The usage and parameters are described as follows:

FROM_UNIXTIME (unix_timestamp, format)
Returns a string representing the Unix time mark, formatted according to the format string. Format can contain the same modifier as the entries listed by the DATE_FORMAT () function.
Format the date value based on the format string. The following modifier can be used in the format string: % M month name (January ...... December)
% W name of the Week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% 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 (00 ...... 31)
% E number of days in the month (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 hour (00 ...... 23)
% K hour (0 ...... 23)
% H hour (01 ...... 12)
% I hour (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 days in a week (0 = Sunday ...... 6 = Saturday)
% U Week (0 ...... 52). Sunday is the first day of the week.
% U Week (0 ...... 52) Monday is the first day of the week.
% A text "% ".

All other characters are not interpreted and copied to the result.

The result is proved by a specific usage:

SELECT FROM_UNIXTIME (1234567890, '% Y-% m-% d % H: % I: % s'); // Of course, this date format can be specified based on the parameters provided above

The preceding query command displays the following results:

In addition, you need to query the data of mysql today, yesterday, last 7 days, last 30 days, and last month. For details, see: mysql query today, yesterday, last 7 days, and last 30 days, methods for data from the last month and the last month

Articles you may be interested in

• Conversion of date and unix timestamps in php and mysql
• How to check MySQL startup time and running time
• 30 methods for optimizing SQL statement query in mysql
• Php summarizes the methods for obtaining the time interval. php displays the time interval of Forum posts.
• Navicat for mysql remote connection to the mySql database prompt 10061,1045 error Solution
• Summary of methods for php to determine whether the date format is correct
• Js outputs current time in due time
• Summary of methods for saving arrays as text formats in php