[MYSQL] Time milliseconds conversion

Java commonly used bigint field save time, usually save time as a large number of numbers, each time the removal needs to be converted in the program, sometimes inconvenient in the program, you can use the MySQL function from_unixtime (unix_timestamp,format).

Example:

Select From_unixtime (1364176514656/+,'%y-%m-%d%h:%i:%s'  as date;

The result is:

 - - Geneva -  - The following:

From_unixtime (Unix_timestamp,format)

Where Unix_timestamp is the field value/1000.

The values that format can use are:

%M month name (January ... December)

%W Week name (Sunday ... Saturday)

%d The date of the month with English prefixes (1st, 2nd, 3rd, etc.). ）

%Y year, number, 4 bit

%y year, number, 2 bit

%a abbreviated weekday name (Sun ... Sat)

Number of days in the month of%d, number (00 ...). 31)

Number of days in%e month, number (0 ... 31)

%m Month, number (01 ... 12)

%c month, number (1 ... 12)

%b Abbreviated month name (Jan ... DEC)

%j Days of the 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, Numbers (00 ... 59)

%r time, 12 hours (Hh:mm:ss [ap]m)

%T time, 24 hours (HH:MM:SS)

%s seconds (00 ... ()%s seconds (00 ... 59)

%p am or PM

%w days in one weeks (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

Percent of a text "%"

Similarly, sometimes we want to convert the datetime type data to the int type to the database, then we need to use the function

Select Unix_timestamp ('2011-05-31 23:59:59');

Results

1306771200

Note Unix_timestamp This function returns only 1970 .... The number of seconds to now. If you count a function as a number of milliseconds, you also need to multiply by 1000

[MYSQL] Time milliseconds conversion