Convert between date and unix timestamp

From LemonWiki共筆
Revision as of 19:07, 20 July 2020 by Planetoid (talk | contribs) (→‎Convert between date and unix timestamp)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

terms of time format[edit]

format \ date 1899/12/31 (1900/1/0) 1900/1/1 1970/1/1 2016/1/1 notes
value of unix timestamp
(the number of seconds since 1970/01/01[1])
-2209075200 -2208988800 0 1451606400
value of Excel DATEVALUE function or LibreOffice DATEVALUE function
(Excel: number of days since 1898/12/31)
0 1 25569 42370 5 digit numbers after 1970/1/1

Icon exclaim.gif VALUE() function on Excel is number of days since 1899/12/31. DATEVALUE of 1900/1/1 will return 1. VALUE() function on Google sheet is number of days since 1899/12/30 [Last visited: 2019-05-22]

Convert between date and unix timestamp[edit]

Online tool

// timestamp: 1552521600
// Is equivalent to:
// 03/14/2019 @ 12:00am (UTC)
// 2019-03-14T00:00:00+00:00 in ISO 8601
// Thu, 14 Mar 2019 00:00:00 +0000 in RFC 822, 1036, 1123, 2822
// Thursday, 14-Mar-19 00:00:00 UTC in RFC 2822
// 2019-03-14T00:00:00+00:00 in RFC 3339

PHP way: convert to date time from unix timestamp[edit]

Using the date() function & date_default_timezone_set

date_default_timezone_set('UTC');
$timestamp = 0;
echo date('Y-m-d H:i:s', $timestamp) . PHP_EOL; // 1970-01-01 00:00:00

date_default_timezone_set("Europe/London");
$timestamp = 1552521600;
echo date('Y-m-d H:i:s', $timestamp) . PHP_EOL; // 2019-03-14 00:00:00

date_default_timezone_set("Asia/Taipei");
$timestamp = 1552521600;
echo date('Y-m-d H:i:s', $timestamp) . PHP_EOL; // 2019-03-14 08:00:00

PHP way: convert to unix timestamp from date time[edit]

Using the strtotime() function

date_default_timezone_set("Europe/London");
echo strtotime('2019-03-14 00:00:00') . PHP_EOL; // 1552521600

date_default_timezone_set("Asia/Taipei");
echo strtotime('2019-03-14 08:00:00') . PHP_EOL; // 1552521600

Excel way: convert to date from unix timestamp[edit]

timezone: UTC

= (VALUE( date_time )-25569)*86400

Excel way: convert to unix timestamp from date[edit]

input data (A2 cell): YYYY/MM/DD (e.g. 2020/09/24 ) or YYYY/MM/DD HH:MM (e.g. 2020/09/24 10:41) =(A2-DATE(1970,1,1))*86400 [2]

timezone: UTC

= TEXT( (Unixtime / 86400) + 25569, "YYYY-mm-dd hh:mm:ss")

MySQL way: convert to date from unix timestamp[edit]

timezone: MySQL server timezone dependent Icon exclaim.gif[3]

SELECT FROM_UNIXTIME(IF(`timestamp_column` = 0, NULL, `timestamp_column`, "%Y-%m-%d %H:%i:%S") 

MySQL way: convert to unix timestamp from date time[edit]

Using UNIX_TIMESTAMP() function

timezone: MySQL server timezone dependent Icon exclaim.gif

  • SELECT UNIX_TIMESTAMP('2011-03-15 18:53:57'); /* return timestamp: 1300186437 */
  • SELECT UNIX_TIMESTAMP(STR_TO_DATE('2011-03-15 18:53:57', '%Y-%m-%d %H:%i:%S')); /* return timestamp: 1300186437 */

References[edit]