Convert between date and unix timestamp - Revision history

Planetoid at 03:31, 15 July 2025

2025-07-15T03:31:44Z

← Older revision		Revision as of 11:31, 15 July 2025
Line 106:		Line 106:
	== References ==		== References ==
	<references />		<references />

			{{Template:Data factory flow}}

	[[Category:PHP]] [[Category:Programming]]		[[Category:PHP]] [[Category:Programming]]

2020-07-20T11:07:08Z

Convert between date and unix timestamp

← Older revision		Revision as of 19:07, 20 July 2020
Line 95:		Line 95:
	SELECT FROM_UNIXTIME(IF(`timestamp_column` = 0, NULL, `timestamp_column`, "%Y-%m-%d %H:%i:%S")		SELECT FROM_UNIXTIME(IF(`timestamp_column` = 0, NULL, `timestamp_column`, "%Y-%m-%d %H:%i:%S")
	</pre>		</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 ==

2020-07-20T10:36:23Z

MySQL way: convert to date from unix timestamp

← Older revision		Revision as of 18:36, 20 July 2020
Line 93:		Line 93:

	<pre>		<pre>
	SELECT FROM_UNIXTIME(IF(`timestamp_column` = 0, NULL, `timestamp_column`, "%Y-%m-%d %h:%i:%s")		SELECT FROM_UNIXTIME(IF(`timestamp_column` = 0, NULL, `timestamp_column`, "%Y-%m-%d %H:%i:%S")
	</pre>		</pre>

2020-07-20T08:08:22Z

MySQL way: convert to date from unix timestamp

← Older revision		Revision as of 16:08, 20 July 2020
Line 90:		Line 90:

	=== MySQL way: convert to date from unix timestamp ===		=== MySQL way: convert to date from unix timestamp ===
	timezone: server timezone dependent {{exclaim}}<ref>[https://www.w3resource.com/mysql/date-and-time-functions/mysql-from_unixtime-function.php MySQL FROM_UNIXTIME() function]</ref>		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>		<pre>

2020-07-20T08:06:49Z

MySQL way: convert to date from unix timestamp

← Older revision		Revision as of 16:06, 20 July 2020
Line 90:		Line 90:

	=== MySQL way: convert to date from unix timestamp ===		=== MySQL way: convert to date from unix timestamp ===
	timezone: server timezone dependent {{exclaim}}		timezone: 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>		<pre>

2020-07-20T07:42:55Z

Convert between date and unix timestamp

← Older revision		Revision as of 15:42, 20 July 2020
Line 87:		Line 87:
	<pre>		<pre>
	= TEXT( (Unixtime / 86400) + 25569, "YYYY-mm-dd hh:mm:ss")		= TEXT( (Unixtime / 86400) + 25569, "YYYY-mm-dd hh:mm:ss")
			</pre>

			=== MySQL way: convert to date from unix timestamp ===
			timezone: server timezone dependent {{exclaim}}

			<pre>
			SELECT FROM_UNIXTIME(IF(`timestamp_column` = 0, NULL, `timestamp_column`, "%Y-%m-%d %h:%i:%s")
	</pre>		</pre>

2019-09-09T06:03:09Z

2019-09-09T06:00:55Z

← Older revision		Revision as of 14:00, 9 September 2019
Line 76:		Line 76:
	* [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>		<pre>
	= (VALUE( date_time )-25569)*86400		= (VALUE( date_time )-25569)*86400
Line 83:		Line 84:
	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>		<pre>
	= TEXT( (Unixtime / 86400) + 25569, "YYYY-mm-dd hh:mm:ss")		= TEXT( (Unixtime / 86400) + 25569, "YYYY-mm-dd hh:mm:ss")

2019-09-07T07:56:46Z

Excel way: convert to unix timestamp from date

← Older revision		Revision as of 15:56, 7 September 2019
Line 82:		Line 82:
	=== 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>

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

	== References ==		== References ==

2019-09-07T07:56:19Z

Excel way: convert to date from unix timestamp

← Older revision		Revision as of 15:56, 7 September 2019
Line 75:		Line 75:
	* [https://errerrors.blogspot.com/2019/09/how-to-convert-between-date-and-unix-timestamp-in-excel.html 怎樣轉換 Excel 日期時間成 Unix 時間戳記 (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?]

			<pre>
			= (VALUE( date_time )-25569)*86400
			</pre>

	=== Excel way: convert to unix timestamp from date ===		=== Excel way: convert to unix timestamp from date ===