mysql時間和日期轉換的一些例子

lujianxin1098發表於2014-01-23
MySql

從時間秒中獲取 當天凌晨0點0分的秒數：

比如根據  1390467136 （2014-01-23 16:52:16）計算得到時間 2014-01-23 00:00:00對應的秒數，

方法如下： SELECT UNIX_TIMESTAMP( DATE_FORMAT(FROM_UNIXTIME( 1390467136 ),'%Y-%m-%d')) FROM DUAL;

查詢結果：1390406400

根據時間秒數得到 星期幾，查詢方法如下：

SELECT WEEKDAY(FROM_UNIXTIME( 1390467136 ,'%Y-%m-%d'))+1 AS wday FROM DUAL;

查詢結果是： 4

下面來看看這裡面用到的一些函式的含義：

函式：FROM_UNIXTIME
作用：將MYSQL中以INT(11)儲存的時間以"YYYY-MM-DD"格式來顯示。
語法：FROM_UNIXTIME(unix_timestamp,format)

返回表示 Unix 時間標記的一個字串，根據format字串格式化。format可以包含與DATE_FORMAT()函式列出的條目同樣的修飾符。

根據format字串格式化date值。
下列修飾符可以被用在format字串中：

%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英語字首的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 數字, 4 位
%y 年, 數字, 2 位
%a 縮寫的星期名字(Sun……Sat)
%d 月份中的天數, 數字(00……31)
%e 月份中的天數, 數字(0……31)
%m 月, 數字(01……12)
%c 月, 數字(1……12)
%b 縮寫的月份名字(Jan……Dec)
%j 一年中的天數(001……366)
%H 小時(00……23)
%k 小時(0……23)
%h 小時(01……12)
%I 小時(01……12)
%l 小時(1……12)
%i 分鐘, 數字(00……59)
%r 時間,12 小時(hh:mm:ss [AP]M)
%T 時間,24 小時(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一個星期中的天數(0=Sunday ……6=Saturday ）
%U 星期(0……52), 這裡星期天是星期的第一天
%u 星期(0……52), 這裡星期一是星期的第一天
%% 一個文字“%”。

例子：

SELECT FROM_UNIXTIME(1390467136, '%Y-%m-%d %H:%i:%S') from dual;

DATE_FORMAT() 函式用於以不同的格式顯示日期/時間資料。

語法

DATE_FORMAT(date,format)

date 引數是合法的日期。format 規定日期/時間的輸出格式。

可以使用的格式有：

格式 描述
%a 縮寫星期名
%b 縮寫月名
%c 月，數值
%D 帶有英文字首的月中的天
%d 月的天，數值(00-31)
%e 月的天，數值(0-31)
%f 微秒
%H 小時 (00-23)
%h 小時 (01-12)
%I 小時 (01-12)
%i 分鐘，數值(00-59)
%j 年的天 (001-366)
%k 小時 (0-23)
%l 小時 (1-12)
%M 月名
%m 月，數值(00-12)
%p AM 或 PM
%r 時間，12-小時（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 時間, 24-小時 (hh:mm:ss)
%U 周 (00-53) 星期日是一週的第一天
%u 周 (00-53) 星期一是一週的第一天
%V 周 (01-53) 星期日是一週的第一天，與 %X 使用
%v 周 (01-53) 星期一是一週的第一天，與 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，與 %V 使用
%x 年，其中的星期一是周的第一天，4 位，與 %v 使用
%Y 年，4 位
%y 年，2 位

例項

下面的指令碼使用 DATE_FORMAT() 函式來顯示不同的格式。我們使用 NOW() 來獲得當前的日期/時間：

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

結果類似：

Dec 29 2008 11:45 PM
12-29-2008
29 Dec 08
29 Dec 2008 16:25:46.635

MySQL中的UNIX_TIMESTAMP函式有兩種型別供呼叫
 
1  無引數呼叫：UNIX_TIMESTAMP() 
返回值：自'1970-01-01 00:00:00'的到當前時間的秒數差
例子：SELECT UNIX_TIMESTAMP()  => 1339123415
 
2  有引數呼叫：UNIX_TIMESTAMP(date)
其中date可以是一個DATE字串，一個DATETIME字串，一個TIMESTAMP或者一個當地時間的YYMMDD或YYYMMDD格式的數字
返回值：自'1970-01-01 00:00:00'與指定時間的秒數差
舉例說明：  www.2cto.com  
DATE字串格式：（日期型別）
SELECT UNIX_TIMESTAMP(‘2012-06-08’)       => 1339084800
SELECT UNIX_TIMESTAMP(CURRENT_DATE())  =>1339084800
注：CURRENT_DATE ()的返回值是一個DATE字串格式
 
以下幾種格式返回的結果相同：
SELECT UNIX_TIMESTAMP('20120608');
SELECT UNIX_TIMESTAMP('2012-6-8');
SELECT UNIX_TIMESTAMP('2012-06-08');
結果都是：1339084800
 
DATETIME字串格式:（日期和時間的組合型別）
SELECT UNIX_TIMESTAMP(‘2012-06-08 10:48:55’)  => 1339123415
SELECT UNIX_TIMESTAMP(NOW())  => 1339123415
注：NOW()的返回值是一個DATETIME字串格式

 MySQL week… 函式：week(), weekofyear(), dayofweek(), weekday(), yearweek()
set @dt = '2008-08-08';
select week(@dt); -- 31
select week(@dt,3); -- 32
select weekofyear(@dt); -- 32
select dayofweek(@dt); -- 6
select weekday(@dt); -- 4
select yearweek(@dt); -- 200831
MySQL week() 函式，可以有兩個引數，具體可看手冊。 weekofyear() 和 week() 一樣，都是計算“某天”是位於一年中的第幾周。 weekofyear(@dt) 等價於 week(@dt,3)。
MySQL weekday() 函式和 dayofweek() 類似，都是返回“某天”在一週中的位置。不同點在於參考的標準， weekday：(0 = Monday, 1 = Tuesday, …, 6 = Sunday)； dayofweek：（1 = Sunday, 2 = Monday, …, 7 = Saturday）
MySQL yearweek() 函式，返回 year(2008) + week 位置(31)。

相關文章