MySQL Timestamp conversion function Unix

1.unix_timestamp

Converts a time to a timestamp. Convert date type data to timestamp timestamp form

Default time stamp for current time

Mysql> select Unix_timestamp ();
+ —————— +
| Unix_timestamp () |
+ —————— +
| 1436775578 |
+ —————— +
1 row in Set (0.01 sec)

Mysql> Select Unix_timestamp (' 2015-07-13 16:20:20 ');
+ ————————————— +
| Unix_timestamp (' 2015-07-13 16:20:20 ') |
+ ————————————— +
| 1436775620 |
+ ————————————— +
1 row in Set (0.00 sec)

2.from_unixtime

Converts a timestamp timestamp form integer to a date type

Mysql> Select From_unixtime (1436775620);
+ ————————— +
| From_unixtime (1436775620) |
+ ————————— +
| 2015-07-13 16:20:20 |
+ ————————— +
1 row in Set (0.00 sec)

You can, of course, specify the time format for the output:

Mysql> Select From_unixtime (1436775620, '%y%m%d ');
+ ———————————— +
| From_unixtime (1436775620, '%y-%m-%d ') |
+ ———————————— +
| 2015-07-13 |
+ ———————————— +

3. Limitations on the MySQL timestamp

At present, the range of timestamp can be expressed between 1970-2038.

The time that you get over this range will overflow and the time you get is null.

Mysql> Select From_unixtime (0);
+ ——————— +
| From_unixtime (0) |
+ ——————— +
| 1970-01-01 08:00:00 |
+ ——————— +

Mysql> Select From_unixtime (2247483647);
+ ————————— +
| From_unixtime (2247483647) |
+ ————————— +
| 2038-01-19 11:14:07 |
+ ————————— +
1 row in Set (0.00 sec)

4, in addition to MySQL PHP can also achieve the same effect, examples are as follows

(full date and time of the month)

Unix timestamp in PHP converted to date function: Date ()

Date (' y-m-d h:i:s ', 1156219870);

PHP date converted to UNIX timestamp function: Strtotime ()

Strtotime (' 2010-03-24 08:15:42 ');

PHP provides functions to easily convert various forms of dates to timestamps, which are mainly:

Strtotime (): Resolves the date-time description of any English text to a timestamp.
Mktime (): Gets the timestamp from the date.
Strtotime ()

The Strtotime () function converts the date represented by an English text string to a timestamp, the inverse of date (), returns a timestamp successfully, or FALSE. Grammar:

int Strtotime (string time [, int now])
The parameter time is the parsed string, which is the date represented by the GNU date input format.

Example:

<?php
Echo strtotime ("2009-10-21 16:00:10"); Output 1256112010
Echo Strtotime ("September 2008"); Output 1220976000
Echo strtotime ("+1 Day"), "<br/>"; Output the timestamp at this time of tomorrow
?>
Mktime ()

The Mktime () function is used to get a timestamp from a date, returning a timestamp successfully, or FALSE. Grammar:

int Mktime (time, minutes, seconds, months, days, years)
Example:

<?php
Echo Mktime (21, 50, 55, 07, 14, 2010); Output "1279115455"
?>
Parameters can be omitted from right to left, and any omitted parameters are set to the current value of the date and time of the cost.

Mktime () is useful for date calculations and validation, and it automatically calculates the correct values for input that goes out of range. For example, the following example output is 2008-01-01:

<?php
echo Date ("Y-m-d", mktime (0, 0, 0, 12, 32, 2007));
echo Date ("Y-m-d", mktime (0, 0, 0, 13, 1, 2007));
?>
The last day of next month. The last day of any given month can be expressed as the first "0" Day of the next month, not 1 days, as in the following example:

<?php
$lastday = mktime (0, 0, 0, 3, 0, 2008);
Echo strftime ("The Last Day of 2008 is:%d", $lastday);
The last day of 2008 is: 29
?>
Custom functions

The following function is similar to the Strtotime function.

<?php
$date _str = "2011-09-11 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);

?>