Convert between date and unix timestamp: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
mNo edit summary |
||
(4 intermediate revisions by the same user not shown) | |||
Line 73: | Line 73: | ||
=== Excel way: convert to date from unix timestamp === | === 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?] | ||
* | |||
timezone: UTC | |||
<pre> | |||
= (VALUE( date_time )-25569)*86400 | |||
</pre> | |||
=== Excel way: convert to unix timestamp from date === | === 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> | 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> | |||
== References == | == References == | ||
Line 85: | Line 95: | ||
[[Category:Web_Dev]] | [[Category:Web_Dev]] | ||
[[Category:MySQL]] | [[Category:MySQL]] | ||
[[Category:Excel]] | |||
[[Category:Data Science]] | [[Category:Data Science]] | ||
[[Category:Time river]] | [[Category:Time river]] |
Revision as of 14:03, 9 September 2019
terms of time format
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 |
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
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
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
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
- 怎樣轉換 Excel 日期時間成 Unix 時間戳記 (unix timestamp)
- How to convert between date and Unix timestamp in Excel?
timezone: UTC
= (VALUE( date_time )-25569)*86400
Excel way: convert to unix timestamp from date
input data (A2 cell): YYYY/MM/DD (e.g. 2024/03/29 ) or YYYY/MM/DD HH:MM (e.g. 2024/03/29 05:46) =(A2-DATE(1970,1,1))*86400 [2]
timezone: UTC
= TEXT( (Unixtime / 86400) + 25569, "YYYY-mm-dd hh:mm:ss")