使用mysql日期與時間函式輕易搞定日期與時間邏輯

徐家三少發表於2017-02-18
MySql函式

mysql查詢當天的資料:

    select* from procurement where date(createDate)=curdate()  order by refreshDatetime   desc ;複製程式碼

mysql查詢過去幾天的資料:

    select * from procurement 
        where DATEDIFF(NOW(),createDate)<6 and DATEDIFF(NOW(),createDate)>=0  where
        order by refreshDatetime   desc複製程式碼

mysql函式無非是三類：獲得時間。設定時間。格式化時間。

curdate函式用來返回當前的日期：

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2014-09-11 |
+------------+
1 row in set

mysql>複製程式碼
mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2014-09-11 |
+------------+
1 row in set

mysql> select curdate()+0;
+-------------+
| curdate()+0 |
+-------------+
|    20140911 |
+-------------+
1 row in set

mysql> 
curtime函式返回當前時間。


mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 16:26:59  |
+-----------+
1 row in set

mysql> select curtime()+0;
+-------------+
| curtime()+0 |
+-------------+
|      162721 |
+-------------+
1 row in set

mysql>複製程式碼

類似的，

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2014-09-11 16:28:49 |
+---------------------+
1 row in set

mysql> select current_timestamp()+0;
+-----------------------+
| current_timestamp()+0 |
+-----------------------+
|        20140911162915 |
+-----------------------+
1 row in set

mysql>複製程式碼

其中：
CURRENT_TIMESTAMP和CURRENT_TIMESTAMP()是NOW()的同義詞

date()用於從一個時間表示式中間提取出日期，它會判斷表示式是否正確，如果不正確，將會得到空：

mysql> select date('2010-11-10');
+--------------------+
| date('2010-11-10') |
+--------------------+
| 2010-11-10         |
+--------------------+
1 row in set

mysql> select date('2010-11-01 12:03:07');
+-----------------------------+
| date('2010-11-01 12:03:07') |
+-----------------------------+
| 2010-11-01                  |
+-----------------------------+
1 row in set

mysql> select date('2010-11-71 12:03:07');
+-----------------------------+
| date('2010-11-71 12:03:07') |
+-----------------------------+
| NULL                        |
+-----------------------------+
1 row in set

mysql> select date('2010-11-71 12:93:07');
+-----------------------------+
| date('2010-11-71 12:93:07') |
+-----------------------------+
| NULL                        |
+-----------------------------+
1 row in set

mysql> 

date函式獲得日期與分隔符無關
mysql> select date('20101101');
+------------------+
| date('20101101') |
+------------------+
| 2010-11-01       |
+------------------+
1 row in set

mysql> select date('2010111111');
+--------------------+
| date('2010111111') |
+--------------------+
| 2020-10-11         |
+--------------------+
1 row in set

mysql> select date('2010x11x1111'); //出錯的原因是因為日是1111，與分隔符無關
+----------------------+
| date('2010x11x1111') |
+----------------------+
| NULL                 |
+----------------------+
1 row in set

mysql> select date('2010*11*11 11');
+-----------------------+
| date('2010*11*11 11') |
+-----------------------+
| 2010-11-11            |
+-----------------------+
1 row in set

mysql> 

得到時間差的函式：

mysql> select datediff('1995-11-09',now());
+------------------------------+
| datediff('1995-11-09',now()) |
+------------------------------+
|                        -6881 |
+------------------------------+
1 row in set

mysql> select datediff(now(),'1995-11-09');
+------------------------------+
| datediff(now(),'1995-11-09') |
+------------------------------+
|                         6881 |
+------------------------------+
1 row in set

mysql>複製程式碼

現在有一個問題，比如需要知道最近三十天登入的使用者，該怎麼做：
一種方法是與PHP結合

<?php
$time=date("Y-m-d",strtotime("-30 day"));
echo $time;
?>複製程式碼

如果精確到秒的話

$time=date("Y-m-d H:i:s",strtotime("-30 day"));複製程式碼

30天前就是-30.
注意：日期是可以直接比較大小的：

select userId from travel_plan where startDate>'2010-10-10';

date_sub函式，返回一個過去的時間差。

mysql> select date_sub(now(),interval 30 day);
+---------------------------------+
| date_sub(now(),interval 30 day) |
+---------------------------------+
| 2014-08-12 17:17:29             |
+---------------------------------+
1 row in set

mysql> select date_sub('2010-11-01',interval 30 day);
+----------------------------------------+
| date_sub('2010-11-01',interval 30 day) |
+----------------------------------------+
| 2010-10-02                             |
+----------------------------------------+
1 row in set

mysql> 

date_add返回一個未來的時間差：
mysql> select date_add('2010-11-01',interval 30 day);
+----------------------------------------+
| date_add('2010-11-01',interval 30 day) |
+----------------------------------------+
| 2010-12-01                             |
+----------------------------------------+
1 row in set

mysql> select date_add(now(),interval 30 day);
+---------------------------------+
| date_add(now(),interval 30 day) |
+---------------------------------+
| 2014-10-11 17:22:46             |
+---------------------------------+
1 row in set

mysql>複製程式碼

我是如何運用這些的
mysql有一個問題，就是比如select now 得到的是系統時間，是根據作業系統當前時間來確定的。
但是使用utc函式的時候，就差8個小時，是根據utc0來確定的，就不再依據系統當前的時區了。

相關文章