Tag: time

PHP: Finding the year quarter for a date

Posted by – November 29, 2012

在工商業應用中,時間紀錄是系統中非常基本的東西,不過時間的表示方法就五花八門了。最近碰到一個需求是同一個時間欄位可能是 Y-m-d (ex. 2012-11-29)、quarter (Q4 2012)、month (Nov 2012),或是 year (2012),甚至是 TBA (to be announced)。

其中轉換成季 (quarter) 會用到一點點技巧,在此筆記如下:

<?
$timestamp = mktime(0, 0, 0, 11, 29, 2012);
echo ceil(date("m", $timestamp)/3);
?>

只要前面再加個 'Q' 就是第幾季了。
在資料庫部份如何分辨客戶是儲存哪種型態,我的做法是多開一個 date_type 的欄位。

CREATE TABLE `calendar` (
	`c_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	`c_date` date NOT NULL,
	`c_date_type` char(1) NOT NULL DEFAULT 'd',
	PRIMARY KEY (`c_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

然後用以下 function 來轉換日期

function date_converter($date_type, $date){

	$timestamp = strtotime($date);

	switch($date_type){
		case 'd':
			return $date;
			break;
		case 'm':
			return date("Y-m-d", mktime(23, 59, 59, date('m', $timestamp) + 1, 0, date('Y', $timestamp)));
			break;
		case 'q':
			$the_quarter = ceil(date("m", $timestamp)/3);
			return date("Y-m-d", mktime(23, 59, 59, $the_quarter * 3 + 1, 0, date('Y', $timestamp)));
			break;
		case 'y':
			return date("Y-m-d", mktime(23, 59, 59, 12, 31, date('Y', $timestamp)));
			break;
		case 't':
			return '2099-12-31';
			break;
	}
}

最後將轉換後的日期與日期型態一份存入資料庫,讀取時根據型態做切換就可以了。

Updated:

後來又碰到一個需求,碰到資料庫日期相同時,例如季和月兩種日期都是記錄最後一天,Q1 與 March 在資料庫裡都是 3 / 31,排序的時候就有可能出現 Q1, March, Q1, March 交錯的現象。解決方法是在 SELECT ... ORDER BY ... 加個自訂排序,用前述的 date_type 來排列。

SELECT * FROM `calendar` ORDER BY `c_date` ASC, FIELD(`c_date_type`, `d`, `m`, `q`, `y`, `t`)

如此就可以避免不同型態日期交錯的問題。

 

[Excel] Convert Date and UNIX time

Posted by – June 2, 2011

這個問題是最近工作上碰到的,把 SQL 裡的 time 欄位輸出,整理成 Excel 給 PM,不過報表裡的時間都是 UNIX time 格式,除非有超強的心算,不然一般人是沒辦法直接看著 UNIX time 換成算時間日期的。

UNIX time 是以 1970 年 1 月 1 日零點 (UTC) 為起點,每過一秒加 1 的時間計算方式,更詳細的介紹請參考維基百科。根據這個公式,本文撰文時刻是 1306971424

Excel 內部的時間計算方式和 UNIX time 有異曲同工之妙,不過起點是 1900 年 1 月 1 日,日期是整數,時間是小數,詳情請參考這篇如何在 Excel 中使用日期與時間。翻遍 Excel 2010 的 Help 也找不到和 UNIX 或 POSIX 相關的 function,看來只能土法轉換了。

轉換公式 =Unixtime / 86400 + 25569

More