How to convert a timestamp to a common date format in MySQL

Store time stamp to MySQL database, use conversion function Unix_timestamp.

Pgsql

The code is as follows	Copy Code
INSERT into Tbl_name (col1) VALUES (Unix_timestamp (' 2012-11-13 13:54:22 ')); 1 INSERT into Tbl_name (col1) VALUES (Unix_timestamp (' 2012-11-13 13:54:22 '));

Another conversion function: From_unixtime, which is to turn the timestamp into a regular time format

Pgsql

The code is as follows	Copy Code
Select <span class= ' wp_keywordlink_affiliate ' ><a href= ' http://lxy.me/tag/from_unixtime ' title= ' view from_ All articles in Unixtime "target=" _blank ">FROM_UNIXTIME</a></span> (xxx_time) from Tbl_name;

1

The code is as follows	Copy Code
Select From_unixtime (xxx_time) from Tbl_name;

1. Convert time to time stamp

The code is as follows	Copy Code
Select Unix_timestamp (' 2009-10-26 10-06-07 ')

If the parameter is empty, the current time is processed
2. Convert time stamp to time

The code is as follows	Copy Code
Select From_unixtime (1256540102)

Some applications generate a timestamp that is three bit more than this, is a millisecond, if you want to convert, you need to first remove the last three bits, otherwise return null

Unix_timestamp (date)

Returns a UNIX timestamp (the number of seconds since ' 1970-01-01 00:00:00 ' GMT) if no parameter calls are invoked. If Unix_timestamp () is invoked with a date parameter, it returns the number of seconds that start from ' 1970-01-01 00:00:00 ' GMT. Date can be a date string, a DateTime string, a timestamp, or a number of local time in YYMMDD or YYYYMMDD format.

The code is as follows	Copy Code
Mysql> select Unix_timestamp (); -> 882226357 Mysql> Select Unix_timestamp (' 1997-10-04 22:23:00 '); -> 875996580

When Unix_timestamp is used in a timestamp column, the function will accept the value directly, without the implied "String-to-unix-timestamp" transformation.

From_unixtime (Unix_timestamp)

Returns the value represented by the Unix_timestamp parameter in ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or a numeric context.

The code is as follows	Copy Code
Mysql> Select From_unixtime (875996580); -> ' 1997-10-04 22:23:00 ' Mysql> Select From_unixtime (875996580) + 0; -> 19971004222300

From_unixtime (Unix_timestamp,format)

Returns a String representing the Unix time token, formatted according to the format string. FORMAT can contain the same modifiers as the entries listed in the Date_format () function.

The code is as follows	Copy Code
Mysql> Select From_unixtime (Unix_timestamp (), '%Y%d%m%h:%i:%s%x '); -> ' 1997 23rd December 03:43:30 X '

Converts the date type data in the MySQL database into a TIMESTAMP form of UNIX using the Unix_timestamp function:

The code is as follows	Copy Code
Select Unix_timestamp (' 2006-02-28 ') testdate;

specific uses of the From_unixtime function:

From_unixtime (Unix_timestamp,format)
Returns a String representing the Unix time token, formatted according to the format string. FORMAT can contain the same modifiers as the entries listed in the Date_format () function.
Formats the date value based on the format string. The following modifiers can be used in the format string:%m month name (January ...). December)
%w Week name (Sunday ...) Saturday)
%d The date of the month with English prefix (1st, 2nd, 3rd, and so on). ）
%Y years, numbers, 4-bit
%y years, numbers, 2-bit
%a name of the week (Sun ...) Sat)
Days in%d months, numbers (00 ...). 31)
%e the number of days in the month, numbers (0 ...). 31)
%m Month, number (01 ...) 12)
%c month, number (1 ...). 12)
%b abbreviated month name (...) DEC)
%j the 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 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 one weeks (0=sunday ...). 6=saturday)
%u weeks (0 .....) 52), here Sunday is the first day of the week
%u weeks (0 .....) 52), here Monday is the first day of the week
Percent% of a text "%".

All other characters do not interpret are copied into the result.

An illustrative example of from_unixtime function customization format

code is as follows	copy code
SELECT from _unixtime (1234567890, '%y-%m-%d%h:%i:%s ');