14,970
edits
Convert between date and unix timestamp: Difference between revisions
Jump to navigation
Jump to search
no edit summary
No edit summary |
|||
| (14 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 | <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 == | ||
| Line 70: | 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?] | ||
* [https:// | |||
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 == | ||
<references /> | <references /> | ||
{{Template:Data factory flow}} | |||
[[Category:PHP]] [[Category:Programming]] | [[Category:PHP]] [[Category:Programming]] | ||
[[Category:Web_Dev]] | [[Category:Web_Dev]] | ||
[[Category:MySQL]] | [[Category:MySQL]] | ||
[[Category:Excel]] | |||
[[Category:Data Science]] | [[Category:Data Science]] | ||
[[Category:Time river]] | [[Category:Time river]] | ||