天天看點

mysql常用日期時間/數值函數

author:skate

time:2013/05/16

1.日期時間函數

時間轉化秒函數:time_to_sec

mysql> select time_to_sec('01:01:01');

+-------------------------+

| time_to_sec('01:01:01') |

+-------------------------+

|                    3661 |

+-------------------------+

1 row in set (0.00 sec)

秒轉化時間函數:sec_to_time

mysql> select sec_to_time(3661);

+-------------------+

| sec_to_time(3661) |

+-------------------+

| 01:01:01          |

+-------------------+

1 row in set (0.00 sec)

日期轉為天數函數:to_days

mysql> select to_days('0000-00-00');

+-----------------------+

| to_days('0000-00-00') |

+-----------------------+

|                  NULL |

+-----------------------+

1 row in set, 1 warning (0.01 sec)

mysql> select to_days('0001-01-01');

+-----------------------+

| to_days('0001-01-01') |

+-----------------------+

|                   366 |

+-----------------------+

1 row in set (0.00 sec)

天數轉化日期函數:from_days

mysql> select from_days(0);

+--------------+

| from_days(0) |

+--------------+

| 0000-00-00   |

+--------------+

1 row in set (0.00 sec)

mysql> select from_days(366);

+----------------+

| from_days(366) |

+----------------+

| 0001-01-01     |

+----------------+

1 row in set (0.00 sec)

字元串轉換為日期函數:str_to_date

mysql> select str_to_date('2013-01-01 01:21:01','%Y-%m-%d %H:%i:%s');

+--------------------------------------------------------+

| str_to_date('2013-01-01 01:21:01','%Y-%m-%d %H:%i:%s') |

+--------------------------------------------------------+

| 2013-01-01 01:21:01                                    |

+--------------------------------------------------------+

1 row in set (0.00 sec)

日期轉換為字元串函數:date_format

mysql> select date_format('2013-01-01 01:21:01','%Y%m%d %H%i%s');

+----------------------------------------------------+

| date_format('2013-01-01 01:21:01','%Y%m%d %H%i%s') |

+----------------------------------------------------+

| 20130101 012101                                    |

+----------------------------------------------------+

1 row in set (0.00 sec)

時間轉換為字元串函數:time_format

mysql> select time_format('01:21:01','%H%i%s');

+----------------------------------+

| time_format('01:21:01','%H%i%s') |

+----------------------------------+

| 012101                           |

+----------------------------------+

1 row in set (0.00 sec)

說明:

日期時間格式參數如下:

%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), 這裡星期一是星期的第一天

%% 一個文字“%”。

提取表達式的日期部分

mysql> select date(now());

+-------------+

| date(now()) |

+-------------+

| 2013-05-16  |

+-------------+

1 row in set (0.00 sec)

傳回表達式的星期索引(0=星期一,1=星期二, ……6= 星期天)。

mysql> select weekday(now());

+----------------+

| weekday(now()) |

+----------------+

|              3 |

+----------------+

1 row in set (0.00 sec)

傳回表達式是一年的第幾周

mysql> select week(now());

+-------------+

| week(now()) |

+-------------+

|          19 |

+-------------+

1 row in set (0.00 sec)

WEEK()允許指定星期是否開始于星期天或星期一。如果第二個參數是0,星期從星期天開始,如果第二個參數是1, 從星期一開始,如下所示:

mysql> select week(now(),0);

+---------------+

| week(now(),0) |

+---------------+

|            19 |

+---------------+

1 row in set (0.00 sec)

mysql> select week(now(),1);

+---------------+

| week(now(),1) |

+---------------+

|            20 |

+---------------+

1 row in set (0.00 sec)

傳回表達式一年中季度

mysql> select quarter(now());

+----------------+

| quarter(now()) |

+----------------+

|              2 |

+----------------+

1 row in set (0.00 sec)

傳回表達式一周的第一天

mysql> select dayofweek(now());

+------------------+

| dayofweek(now()) |

+------------------+

|                5 |

+------------------+

1 row in set (0.00 sec)

傳回表達式一個月的第幾天

mysql> select dayofmonth(now());

+-------------------+

| dayofmonth(now()) |

+-------------------+

|                16 |

+-------------------+

1 row in set (0.00 sec)

傳回表達式一年的第幾天

mysql> select dayofyear(now());

+------------------+

| dayofyear(now()) |

+------------------+

|              136 |

+------------------+

1 row in set (0.00 sec)

傳回表達式的星期名字

mysql> select dayname(now());

+----------------+

| dayname(now()) |

+----------------+

| Thursday       |

+----------------+

1 row in set (0.00 sec)

傳回表達式月份的名字

mysql> select monthname(now());

+------------------+

| monthname(now()) |

+------------------+

| May              |

+------------------+

1 row in set (0.00 sec)

mysql>

提取表達式的年份

mysql> select year(now());

+-------------+

| year(now()) |

+-------------+

|        2013 |

+-------------+

1 row in set (0.00 sec)

提取表達式的月份

mysql> select month(now());

+--------------+

| month(now()) |

+--------------+

|            5 |

+--------------+

1 row in set (0.01 sec)

提取表達式的天數

mysql> select day(now());

+------------+

| day(now()) |

+------------+

|         16 |

+------------+

1 row in set (0.00 sec)

提取表達式的小時

mysql> select hour(now());

+-------------+

| hour(now()) |

+-------------+

|          16 |

+-------------+

1 row in set (0.00 sec)

提取表達式的分鐘

mysql> select minute(now());

+---------------+

| minute(now()) |

+---------------+

|            31 |

+---------------+

1 row in set (0.00 sec)

提取表達式的秒數

mysql> select second(now());

+---------------+

| second(now()) |

+---------------+

|            34 |

+---------------+

1 row in set (0.00 sec)

将目前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值傳回,具體格式根據函數用在字元串或是數字語境中而定。

mysql> select curdate();

+------------+

| curdate()  |

+------------+

| 2013-05-16 |

+------------+

1 row in set (0.00 sec)

mysql> select curdate()+1;

+-------------+

| curdate()+1 |

+-------------+

|    20130517 |

+-------------+

1 row in set (0.00 sec)

将目前時間以'HH:MM:SS'或 HHMMSS的格式傳回,具體格式根據函數用在字元串或是數字語境中而定。

mysql> select curtime();

+-----------+

| curtime() |

+-----------+

| 16:43:10  |

+-----------+

1 row in set (0.00 sec)

mysql> select curtime()+1;

+---------------+

| curtime()+1   |

+---------------+

| 164420.000000 |

+---------------+

1 row in set (0.00 sec)

擷取目前日期時間:sysdate(),now()

mysql> select sysdate(),sleep(2),sysdate();

+---------------------+----------+---------------------+

| sysdate()           | sleep(2) | sysdate()           |

+---------------------+----------+---------------------+

| 2013-05-16 17:16:04 |        0 | 2013-05-16 17:16:06 |

+---------------------+----------+---------------------+

1 row in set (2.00 sec)

mysql> select now(),sleep(2),now();

+---------------------+----------+---------------------+

| now()               | sleep(2) | now()               |

+---------------------+----------+---------------------+

| 2013-05-16 17:16:18 |        0 | 2013-05-16 17:16:18 |

+---------------------+----------+---------------------+

1 row in set (2.00 sec)

從上面可以看到sysdate和now的差別,now表示語句開始的時間,而sysdate實時的擷取時間

将目前日期按照'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式的值傳回,具體格式根據函數用在字元串或是數字語境中而定。

mysql> select current_timestamp;

+---------------------+

| current_timestamp   |

+---------------------+

| 2013-05-16 17:19:51 |

+---------------------+

1 row in set (0.00 sec)

mysql> select current_timestamp+1;

+-----------------------+

| current_timestamp+1   |

+-----------------------+

| 20130516172008.000000 |

+-----------------------+

1 row in set (0.00 sec)

unix_timestamp(),unix_timestamp(date)

如果沒有參數調用,傳回一個Unix時間戳記(從'1970-01-01 00:00:00'GMT開始的秒數)。如果UNIX_TIMESTAMP()用一

個date參數被調用,它傳回從'1970-01-01 00:00:00' GMT開始的秒數值。date可以是一個DATE字元串、一個DATETIME

字元串、一個TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地時間的一個數字。

mysql> select unix_timestamp();

+------------------+

| unix_timestamp() |

+------------------+

|       1368696216 |

+------------------+

1 row in set (0.00 sec)

mysql> select unix_timestamp('2013-05-16 01:01:01');

+---------------------------------------+

| unix_timestamp('2013-05-16 01:01:01') |

+---------------------------------------+

|                            1368637261 |

+---------------------------------------+

1 row in set (0.00 sec)

mysql>

FROM_UNIXTIME(unix_timestamp)

以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式傳回unix_timestamp參數所表示的值,具體格式根據函數用在字元串或是數字語境中而定

mysql> select from_unixtime(1368637261);

+---------------------------+

| from_unixtime(1368637261) |

+---------------------------+

| 2013-05-16 01:01:01       |

+---------------------------+

1 row in set (0.00 sec)

mysql> select from_unixtime(1368637261)+1;

+-----------------------------+

| from_unixtime(1368637261)+1 |

+-----------------------------+

|       20130516010102.000000 |

+-----------------------------+

1 row in set (0.00 sec)

mysql> select from_unixtime(1368637261,'%Y-%m-%d %h:%i:%s');

+-----------------------------------------------+

| from_unixtime(1368637261,'%Y-%m-%d %h:%i:%s') |

+-----------------------------------------------+

| 2013-05-16 01:01:01                           |

+-----------------------------------------------+

1 row in set (0.00 sec)

傳回表達式所在月的最後一天

mysql> select last_day(now());

+-----------------+

| last_day(now()) |

+-----------------+

| 2013-05-31      |

+-----------------+

1 row in set (0.00 sec)

日期加減運算

DATE_ADD(date,INTERVAL expr type) --加法

DATE_SUB(date,INTERVAL expr type) --減法

mysql> select date_add('2013-05-16 01:01:01',interval 1 second);

+---------------------------------------------------+

| date_add('2013-05-16 01:01:01',interval 1 second) |

+---------------------------------------------------+

| 2013-05-16 01:01:02                               |

+---------------------------------------------------+

1 row in set (0.00 sec)

mysql> select date_add('2013-05-16 01:01:01',interval 1 day);

+------------------------------------------------+

| date_add('2013-05-16 01:01:01',interval 1 day) |

+------------------------------------------------+

| 2013-05-17 01:01:01                            |

+------------------------------------------------+

1 row in set (0.00 sec)

mysql> select date_add('2013-05-16 01:01:01',interval 1 minute);

+---------------------------------------------------+

| date_add('2013-05-16 01:01:01',interval 1 minute) |

+---------------------------------------------------+

| 2013-05-16 01:02:01                               |

+---------------------------------------------------+

1 row in set (0.00 sec)

mysql> select date_add('2013-05-16 01:01:01',interval 1 hour);

+-------------------------------------------------+

| date_add('2013-05-16 01:01:01',interval 1 hour) |

+-------------------------------------------------+

| 2013-05-16 02:01:01                             |

+-------------------------------------------------+

1 row in set (0.00 sec)

mysql> select date_add('2013-05-16 01:01:01',interval '1:1' minute_second);

+--------------------------------------------------------------+

| date_add('2013-05-16 01:01:01',interval '1:1' minute_second) |

+--------------------------------------------------------------+

| 2013-05-16 01:02:02                                          |

+--------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select date_add('2013-05-16 01:01:01', interval '1 1:1:1' day_second);

+----------------------------------------------------------------+

| date_add('2013-05-16 01:01:01', interval '1 1:1:1' day_second) |

+----------------------------------------------------------------+

| 2013-05-17 02:02:02                                            |

+----------------------------------------------------------------+

1 row in set (0.00 sec)

type值格式:

SECOND 秒 SECONDS

MINUTE 分鐘 MINUTES

HOUR 時間 HOURS

DAY 天 DAYS

MONTH 月 MONTHS

YEAR 年 YEARS

MINUTE_SECOND 分鐘和秒 "MINUTES:SECONDS"

HOUR_MINUTE 小時和分鐘 "HOURS:MINUTES"

DAY_HOUR 天和小時 "DAYS HOURS"

YEAR_MONTH 年和月 "YEARS-MONTHS"

HOUR_SECOND 小時, 分鐘, "HOURS:MINUTES:SECONDS"

DAY_MINUTE 天, 小時, 分鐘 "DAYS HOURS:MINUTES"

DAY_SECOND 天, 小時, 分鐘, 秒 "DAYS HOURS:MINUTES:SECONDS"

DATEDIFF(expr, expr2)

傳回起始時間 expr和結束時間expr2之間的天數。Expr和expr2為日期或 date-and-time 表達式。計算中隻用到這些值的日期部分。

mysql> select datediff('2013-05-16 06:01:01', '2013-05-17 01:01:01');

+--------------------------------------------------------+

| datediff('2013-05-16 06:01:01', '2013-05-17 01:01:01') |

+--------------------------------------------------------+

|                                                     -1 |

+--------------------------------------------------------+

1 row in set (0.00 sec)

表示日期時間的資料類型:

date

time

year

datetime

timestamp

在使用日期時間資料比較時常用如下

mysql> select * from tab ;

+------+---------------------+

| name | createtime          |

+------+---------------------+

| aaaa | 2013-05-14 17:20:19 |

| bbbb | 2013-04-14 17:20:36 |

| bbbb | 2013-04-13 17:20:36 |

| bbbb | 2013-04-15 17:20:36 |

+------+---------------------+

4 rows in set (0.00 sec)

mysql> select now();

+---------------------+

| now()               |

+---------------------+

| 2013-05-14 17:10:26 |

+---------------------+

1 row in set (0.00 sec)

mysql> select * from tab where createtime > now();

+------+---------------------+

| name | createtime          |

+------+---------------------+

| aaaa | 2013-05-14 17:20:19 |

+------+---------------------+

1 row in set (0.00 sec)

mysql> select current_timestamp;

+---------------------+

| current_timestamp   |

+---------------------+

| 2013-05-14 17:10:49 |

+---------------------+

1 row in set (0.00 sec)

mysql> select * from tab where createtime > current_timestamp;

+------+---------------------+

| name | createtime          |

+------+---------------------+

| aaaa | 2013-05-14 17:20:19 |

+------+---------------------+

1 row in set (0.00 sec)

mysql> select * from tab where createtime> str_to_date('2013-05-14 00:00:00','%Y-%m-%d %H:%i:%s');;

+------+---------------------+

| name | createtime          |

+------+---------------------+

| aaaa | 2013-05-14 17:20:19 |

+------+---------------------+

1 row in set (0.00 sec)

mysql> select * from tab where createtime between str_to_date('2013-05-14 00:00:00','%Y-%m-%d %H:%i:%s') and str_to_date('2013-05-15 00:00:00','%Y-%m-%d %H:%i:%s')

    -> ;

+------+---------------------+

| name | createtime          |

+------+---------------------+

| aaaa | 2013-05-14 17:20:19 |

+------+---------------------+

1 row in set (0.00 sec)

mysql> select * from tab where createtime between '2013-05-14 00:00:00' and '2013-05-15 00:00:00';

+------+---------------------+

| name | createtime          |

+------+---------------------+

| aaaa | 2013-05-14 17:20:19 |

+------+---------------------+

1 row in set (0.00 sec)

mysql>

2.數值函數

ABS(X) :傳回表達式X的絕對值

mysql> select abs(-2);

+---------+

| abs(-2) |

+---------+

|       2 |

+---------+

1 row in set (0.00 sec)

FLOOR(X) :傳回不大于X的最大整數值

mysql> select floor(-2.45);

+--------------+

| floor(-2.45) |

+--------------+

|           -3 |

+--------------+

1 row in set (0.00 sec)

MOD(N,M):模操作,傳回N被M除後的餘數。

mysql> select mod(3,2);

+----------+

| mod(3,2) |

+----------+

|        1 |

+----------+

1 row in set (0.00 sec)

RAND()/RAND(N) :傳回一個随機浮點值數a,範圍在 0 到1 之間 (即, 其範圍為 0 ≤ a ≤ 1.0)。若已指定一個整數參數 N,則它被用作種子值,用來産生重複序列。

mysql> select rand();

+-------------------+

| rand()            |

+-------------------+

| 0.294932589209576 |

+-------------------+

1 row in set (0.00 sec)

mysql> select rand(2);

+-------------------+

| rand(2)           |

+-------------------+

| 0.655586646549019 |

+-------------------+

1 row in set (0.00 sec)

ROUND(X)/ROUND(X,D) :傳回參數X, 其值接近于最近似的整數。在有兩個參數的情況下,傳回 X ,其值保留到小數點後D位,而第D位的保留方式為四舍五入。若要接保留X值小數點左邊的D位,可将D設為負值。

mysql> select round(2.4 );

+-------------+

| round(2.4 ) |

+-------------+

|           2 |

+-------------+

1 row in set (0.00 sec)

mysql> select round(2.432,2 );

+-----------------+

| round(2.432,2 ) |

+-----------------+

|            2.43 |

+-----------------+

1 row in set (0.00 sec)

mysql> select round(12.432,-1 );

+-------------------+

| round(12.432,-1 ) |

+-------------------+

|                10 |

+-------------------+

1 row in set (0.00 sec)

-------續------