MySQL console integer time and IP address query function call

In the past two days, a QA asked, "it is difficult to check the Database console because the database storage time is BigInt. I really don't know what it is!

There is related function conversion in the impression, as follows:

The storage time of the mysql database. Generally, the BigInt type, 10 bits, 13 BITs, or more are designed, of which 13 BITs are milliseconds. However, when querying the database time through the Console, bigint is an integer and cannot be viewed.

Mysql provides functions for date conversion.

The FROM_UNIXTIME function converts the corresponding big integer to the corresponding date, And the UNIX_TIMESTAMP function is a reverse operation.

Example:

Select FROM_UNIXTIME (1344834077); ------ 13:01:17. 0

Select UNIX_TIMESTAMP ('2017-08-13 13:01:17. 0'); ----- 2012

The above 10 bits are used. For 13 BITs, because mysql does not support the millisecond level, you need to remove the corresponding millisecond field, that is, the last three bits, such:

Select FROM_UNIXTIME (1344834077887/1000); ------------- 2012-08-13 13:01:17. 0

The function is also followed by '% Y % D % M % h: % I: % s % x' for the corresponding format date format, output the desired date!

For details, refer to the official website: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

The function expression can be a corresponding field.

The IP address has the same problem when the time is solved. (Ps: Database IP fields have integers, which play an important role in data mining and anti-spam)

The corresponding functions are:

INET_ATON (expr): A "Point address" (such as 127.0.0.1) indicating a network address as a string, and an integer representing the address value is returned.

SELECT INET_ATON ('192. 168.130.55 '); ----------- (192 );

INET_NTOA (expr): returns the representation of this address as a string.

Select INET_NTOA (3232268855); ------------------- 192.168.130.55

The above are several functions that are useful in queries!

(Ps: the above functions are only called during query. In actual use, the related conversion is still implemented in the application layer tool class, because the performance of the related conversion and data calls at the application layer are controllable)