Convert between date and unix timestamp: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
 
(17 intermediate revisions by the same user not shown)
Line 4: Line 4:
<tr>
<tr>
   <th> format \ date </th>
   <th> format \ date </th>
  <th> 1899/12/31 (1900/1/0)</th>
   <th> 1900/1/1</th>
   <th> 1900/1/1</th>
   <th> 1970/1/1</th>
   <th> 1970/1/1</th>
Line 12: Line 13:
   <td> value of unix timestamp <br /> (the number of seconds since 1970/01/01<ref>[https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html How to convert between date and Unix timestamp in Excel?]</ref>)</td>
   <td> value of unix timestamp <br /> (the number of seconds since 1970/01/01<ref>[https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html How to convert between date and Unix timestamp in Excel?]</ref>)</td>
   <td> -2209075200</td>
   <td> -2209075200</td>
  <td> -2208988800</td>
   <td> 0</td>
   <td> 0</td>
   <td> 1451606400</td>
   <td> 1451606400</td>
Line 17: Line 19:
</tr>
</tr>
<tr>
<tr>
   <td> value of Excel [https://support.office.com/zh-tw/article/DATEVALUE-%E5%87%BD%E6%95%B8-df8b07d4-7761-4a93-bc33-b7471bbff252 DATEVALUE] function <br />  (Excel: number of days since 1900/1/1) </td>
   <td> value of Excel [https://support.office.com/zh-tw/article/DATEVALUE-%E5%87%BD%E6%95%B8-df8b07d4-7761-4a93-bc33-b7471bbff252 DATEVALUE] function or LibreOffice [https://help.libreoffice.org/Calc/DATEVALUE/zh-TW DATEVALUE] function<br />  (Excel: number of days since 1898/12/31) </td>
  <td> 0</td>
   <td> 1</td>
   <td> 1</td>
   <td> 25569</td>
   <td> 25569</td>
Line 26: Line 29:
</div>
</div>


{{exclaim}} [https://support.office.com/zh-tw/article/value-%E5%87%BD%E6%95%B8-257d0108-07dc-437d-ae1c-bc2d3953d8c2 VALUE()] function on Excel is  number of days since 1900/1/1. [https://support.google.com/docs/answer/3094220?hl=en VALUE()] function on Google sheet is number of days since 1899/12/30 {{access | date=2019-05-22}}
{{exclaim}} [https://support.office.com/zh-tw/article/value-%E5%87%BD%E6%95%B8-257d0108-07dc-437d-ae1c-bc2d3953d8c2 VALUE()] function on Excel is  number of days since 1899/12/31. DATEVALUE of 1900/1/1 will return 1. [https://support.google.com/docs/answer/3094220?hl=en VALUE()] function on Google sheet is number of days since 1899/12/30 {{access | date=2019-05-22}}


== Convert between date and unix timestamp ==
== Convert between date and unix timestamp ==
Online tool
Online tool
* {{Gd}} [https://www.unixtimestamp.com/index.php Unix Time Stamp - Epoch Converter]
* {{Gd}} [https://www.unixtimestamp.com/index.php Unix Time Stamp - Epoch Converter]


PHP way: convert to date from unix timestamp
<pre>
<pre>
// timestamp: 1552521600
// timestamp: 1552521600
Line 41: Line 44:
// Thursday, 14-Mar-19 00:00:00 UTC in RFC 2822
// Thursday, 14-Mar-19 00:00:00 UTC in RFC 2822
// 2019-03-14T00:00:00+00:00 in RFC 3339
// 2019-03-14T00:00:00+00:00 in RFC 3339
</pre>
=== PHP way: convert to date time from unix timestamp ===
Using the [https://www.php.net/manual/en/function.date.php date()] function & [https://www.php.net/manual/en/function.date-default-timezone-set.php date_default_timezone_set]
<pre>
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");
date_default_timezone_set("Europe/London");
Line 51: Line 62:
</pre>
</pre>


Excel way: convert to date from unix timestamp
=== PHP way: convert to unix timestamp from date time ===
Using the [https://www.php.net/manual/en/function.strtotime.php strtotime()] function
<pre>
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
</pre>
 
=== Excel way: convert to date from unix timestamp ===
* [https://errerrors.blogspot.com/2019/09/how-to-convert-between-date-and-unix-timestamp-in-excel.html 怎樣轉換 Excel 日期時間成 Unix 時間戳記 (unix timestamp)]
* [https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html How to convert between date and Unix timestamp in Excel?]
* [https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html How to convert between date and Unix timestamp in Excel?]
* [https://docs.google.com/spreadsheets/d/1mUPLWLdCHcN5Nr3CL2JWYfdYmy4JyiYClXD1kHMLZc4/edit?usp=sharing demo]
 
timezone: UTC
<pre>
= (VALUE( date_time )-25569)*86400
</pre>
 
=== Excel way: convert to unix timestamp from date ===
input data (A2 cell): YYYY/MM/DD (e.g. {{CURRENTYEAR}}/{{CURRENTMONTH}}/{{CURRENTDAY2}} ) or YYYY/MM/DD HH:MM (e.g. {{CURRENTYEAR}}/{{CURRENTMONTH}}/{{CURRENTDAY2}} {{CURRENTTIME}}) {{kbd | key==(A2-DATE(1970,1,1))*86400}} <ref>[http://stackoverflow.com/questions/1703505/excel-date-to-unix-timestamp Excel date to Unix timestamp - Stack Overflow]</ref>
 
timezone: UTC
<pre>
= TEXT( (Unixtime / 86400) + 25569, "YYYY-mm-dd hh:mm:ss")
</pre>
 
=== MySQL way: convert to date from unix timestamp ===
timezone: MySQL server timezone dependent {{exclaim}}<ref>[https://www.w3resource.com/mysql/date-and-time-functions/mysql-from_unixtime-function.php MySQL FROM_UNIXTIME() function]</ref>
 
<pre>
SELECT FROM_UNIXTIME(IF(`timestamp_column` = 0, NULL, `timestamp_column`, "%Y-%m-%d %H:%i:%S")
</pre>
 
=== MySQL way: convert to unix timestamp from date time ===
Using [https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_unix-timestamp UNIX_TIMESTAMP()] function
 
timezone: MySQL server timezone dependent {{exclaim}}
 
* {{kbd | key = SELECT UNIX_TIMESTAMP('2011-03-15 18:53:57');}} /* return timestamp: 1300186437 */
* {{kbd | key = SELECT UNIX_TIMESTAMP(STR_TO_DATE('2011-03-15 18:53:57', '%Y-%m-%d %H:%i:%S'));}} /* return timestamp: 1300186437 */


== References ==
== References ==
Line 61: Line 110:
[[Category:Web_Dev]]
[[Category:Web_Dev]]
[[Category:MySQL]]
[[Category:MySQL]]
[[Category:Excel]]
[[Category:Data Science]]
[[Category:Data Science]]
[[Category:Time river]]

Latest revision as of 19:07, 20 July 2020

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. 2024/03/29 ) or YYYY/MM/DD HH:MM (e.g. 2024/03/29 09:58) =(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]