Mysql timestamp conversion functions unix_timestamp and from_unixtime usage

1. unix_timestamp

Converts a time to a timestamp. Convert data of the date type to the timestamp format

The timestamp of the current time is used by default.

Mysql> select unix_timestamp ();
+ ------ +
| Unix_timestamp () |
+ ------ +
| 1, 1436775578 |
+ ------ +
1 row in set (0.01 sec)

 

Mysql> select unix_timestamp ('2017-07-13 16:20:20 ');
+ ------------- +
| Unix_timestamp ('2017-07-13 16:20:20 ') |
+ ------------- +
| 1, 1436775620 |
+ ------------- +
1 row in set (0.00 sec)

 

2. from_unixtime

Converts an integer in the timestamp format to the date type.

Mysql> select from_unixtime (1436775620 );
+ --------- +
| From_unixtime (1436775620) |
+ --------- +
| 16:20:20 |
+ --------- +
1 row in set (0.00 sec)

Of course, you can also specify the output time format:

Mysql> select from_unixtime (1436775620, '% Y % m % d ');
+ ------------ +
| From_unixtime (1436775620, '% Y-% m-% d') |
+ ------------ +
| 2015-07-13 |
+ ------------ +

 

3. mysql timestamp restrictions

Currently, the range of timestamp is between 1970 and 2038.

If the time exceeds this range, the overflow time is null.

Mysql> select from_unixtime (0 );
+ ------- +
| From_unixtime (0) |
+ ------- +
| 08:00:00 |
+ ------- +

Mysql> select from_unixtime (2247483647 );
+ --------- +
| From_unixtime (2247483647) |
+ --------- +
| 11:14:07 |
+ --------- +
1 row in set (0.00 sec)

4. In addition to mysql php, the same effect can be achieved. The example is as follows:

(Full year, month, day, hour, minute, second)
 
Function for converting a UNIX timestamp in php to a date: date ()
 
Date ('Y-m-d H: I: S', 1156219870 );
 
Convert a date to a UNIX timestamp in php using the strtotime () function ()
 
Strtotime ('2017-03-24 08:15:42 ');

PHP provides functions to easily convert various forms of date into timestamps. These functions are mainly:

Strtotime (): parses the date and time description of any English text into a timestamp.
Mktime (): Get the timestamp from the date.
Strtotime ()

The strtotime () function is used to convert a date represented by a string in English text to a timestamp, which is an inverse function of date (). A timestamp is successfully returned; otherwise, FALSE is returned. Syntax:

Int strtotime (string time [, int now])
The time parameter is a parsed string and is a date in the GNU date input format.

Example:

<? Php
Echo strtotime ("16:00:10"); // output 1256112010
Echo strtotime ("10 September 2008"); // output 1220976000
Echo strtotime ("+ 1 day"), "<br/>"; // output the timestamp at this time tomorrow
?>
Mktime ()

The mktime () function is used to obtain the timestamp from the date. If the timestamp is successfully returned, FALSE is returned. Syntax:

Int mktime (hour, minute, second, month, day, year)
Example:

<? Php
Echo mktime (21, 50, 55, 07, 14,201 0); // output "1279115455"
?>
The parameter can be omitted from the right to the left. Any omitted parameter is set to the current value of the local date and time.

Mktime () is useful for date calculation and verification. It automatically calculates the correct value of the input out of the range. For example, the output in the following example is 2008-01-01:

<? Php
Echo date ("Y-m-d", mktime (0, 0, 0, 12, 32,200 7 ));
Echo date ("Y-m-d", mktime (0, 0, 0, 13, 1, 2007 ));
?>
The last day of the next month. The last day of any given month can be expressed as the "0" day of the next month, rather than the-1 day, as shown in the following example:

<? Php
$ Lastday = mktime (0, 0, 0, 3, 0, and 2008 );
Echo strftime ("The last day of June is: % d", $ lastday );
// The last day of July 29 is: 29
?>
Custom functions

The following functions are similar to the strtotime function.

<? Php
$ Date_str = "17:00:00 ";
Echo $ time_str = str_format_time ($ date_str );

Function str_format_time ($ timestamp = '')
{  
If (preg_match ("/[0-9] {4}-[0-9] {1, 2}-[0-9] {1, 2} (0 [0-9] | 1 [0-9] | 2 [0-3]) :( [0-5] [0-9]) :( [0-5] [0-9])/I ", $ timestamp ))
 {
List ($ date, $ time) = explode ("", $ timestamp );
List ($ year, $ month, $ day) = explode ("-", $ date );
List ($ hour, $ minute, $ seconds) = explode (":", $ time );
$ Timestamp = gmmktime ($ hour, $ minute, $ seconds, $ month, $ day, $ year );
 }
Else
 {
$ Timestamp = time ();
 }
Return $ timestamp;
}

Echo '<br/> ';
Echo date ("Y-m-d H: I: s", $ time_str );

?>