UNIX timestamp is a kind of time representation, which is defined as the total number of seconds from 00:00:00 GMT on January 1, 1970 to now.

How to format timestamp in MySQL?

In mysql, because timestamp cannot support MS, MS is often used for storage. How to store the int type time in the database, such as:

one billion three hundred and forty-four million nine hundred and fifty-four thousand five hundred and fifteen

To a time format that we can normally understand with the naked eye?

We usually use from_ Unixtime function.

FROM_ UNIXTIME(unix_ timestamp,format)

unix_ Timestamp is the time stamp to be processed (the parameter is UNIX time stamp). It can be a field name or a UNIX time stamp string directly.

Format is the format to be converted

The explanation of MySQL official manual is: return the UNIX format value of ‘yyyy-mm-dd HH: mm: Ss’ or yyyymmddhhmmss_ The format of the timestamp parameter depends on whether the function is used in string or numeric context. If format has been given, the format of the result depends on the format string.

The format of format is consistent with the time format of daily programming language.

For example:

mysql> select FROM_UNIXTIME(1344954515,'%Y-%m-%d %H:%i:%S'); 
+-----------------------------------------------+ 
| FROM_UNIXTIME(1344954515,'%Y-%m-%d %H:%i:%S') | 
+-----------------------------------------------+ 
| 2012-08-14 22:28:35                           | 
+-----------------------------------------------+ 
1 row in set (0.00 sec)

UNIX_ Timestamp() is the relative function of time

UNIX_ TIMESTAMP(), UNIX_ TIMESTAMP(date)

The official manual explains that if it is called without parameters, a UNIX timestamp (seconds after ‘1970-01-01 00:00:00’ GMT) is returned as an unsigned integer. If you use date to call UNIX_ Timestamp (), which returns the parameter value in seconds after ‘1970-01-01 00:00:00’ GMT.

Date can be a date string, a datetime string, a timestamp or a number in yymmdd or yymmdd format of local time.

For example:

mysql> select UNIX_TIMESTAMP('2012-09-04 18:17:23'); 
+---------------------------------------+ 
| UNIX_TIMESTAMP('2012-09-04 18:17:23') | 
+---------------------------------------+ 
|                            1346753843 | 
+---------------------------------------+ 
1 row in set (0.00 sec)

In practical application, we can convert the format to millisecond format for comparison with the database, and we can also convert the int format of the database to ordinary format for comparison.

For example, query all blog data created after 18:00:00 on September 3, 2012

Method 1: convert the date to int

select * from blog where createdTime > UNIX_TIMESTAMP('2012-09-03 18:00:00' );

Method 2: convert int to time format

select * from blog where FROM_UNIXTIME(createdTime, '%Y-%m-%d %H:%i:%S') > '2012-09-03 18:00:00';

The following modifiers can be used in the format string to combine some common date formats:

%Name of month m (January… December)

%W week name (Sunday… Sunday)

%D the date of the month prefixed with English (1st, 2nd, 3rd, etc.)

%Year y, number, 4 digits

%Y year, number, 2 digits

%A abbreviated name of the week (sun… SAT)

%Days in month D, number (00… 31)

%Number of days in month e (0… 31)

%Month m, number (01… 12)

%Month C, 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)

%I minutes, numbers (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), where Sunday is the first day of the week

%Monday is the first day of the week

%%A text “%”

This article was first published on duwai’s official blog. Please indicate the source for Reprint: http://www.duwaiweb.com/blog/20120904_ ce7309cf-e25a-45f7-8397-0700fa459466.html

For more Java graduation projects, please visit: http://www.lunwenhui.com/java/page_ 5.html