[SQL] 資料庫時間處理與查詢
dateadd() 時間加減
dateadd(單位, 多少單位, 欄位/func())範例：
select dateadd(day, 5, getdate()) /*現在時間加五天*/
select dateadd(day, -20, getdate()) /*現在時間減20天*/
select getdate() /*取得現在時間*/
select dateadd(day, 1, ‘2018/2/28 0:0:0’) /*確認是否有2/29*/註：每家資料庫的function都不同<oracle/MSSQL/Access都不同>
datediff() 計算兩個時間的差距
語法：DATEDIFF ( datepart , startdate , enddate )
範例：計算table1裡的dd欄位，其欄位值裡的時間與現在時間差多少天。
select datediff(day, getdate(), dd) from table1
進階練習：計算userinfo裡的會員年齡。
--userinfo 每個人的年齡
--Wrong Ans_有BUG (用年份去算會造成多算一歲)
SELECT userinfo.uid as '身分證', cname as '姓名', datediff(year,userinfo.bday,getdate()) as '年齡'
FROM userinfo
--Wrong Ans2_有BUG (超過一定數字後的差異不見了)
SELECT userinfo.uid as 'ID No.', cname as 'Name', datediff(year,userinfo.bday,getdate())/365 as 'Age'
FROM userinfo
--正解_使用天數除以365.25計算年齡
SELECT userinfo.uid as ‘ID No.’, cname as ‘Name’, bday as Birthday, floor(datediff(day,userinfo.bday,getdate())/365.25 ) + 1 as 'Age'
FROM userinfo
--(一年實際上的長度是365.24222天，出生算一歲則要再加1)datepart() 取出時間格式中的特定部分
語法：
datepart(要選取的單位, 被選取的時間)
/*取出現在時間的星期部分*/
datepart(weekday, getdate())
/*取出現在時間的西元年分*/
datepart(year, getdate())datename() 回傳星期幾的文字型態
語法：DATENAME(interval, date)
範例：
/*今天是星期幾*/
datename(dw, getdate())
/*現在時間再加17天是星期幾*/
datename(dw, dateadd(day, 17, getdate()))getUTCdate() 回傳格林威治時間
語法：getutcdate()
範例：現在的台北時間(格林威治時間加八小時)
dateadd(hour, 8, getutcdate())日期時間字串的表示法
'2018/1/8 12:32:18.764' (時間為24小時制)查詢指定時間的資料
範例：查詢日期為2018/1月份的資料(logd為欄位名稱)select * from table1
where
datepart(year, logd) = 2018 and
datepart(mm. logd) = 1查詢某個時段的資料
範例：1. 列出2017年10月1日至2018年1月3日資料
select * from table1
where
logd Between '2017/10/1' And '2018/1/3'2. 列出2017年12月23號18:10:00到現在資料
select * from table1
where
logd Between '2017/12/23 18:10:00' And GetDate()查詢不規則時段的資料
SELECT * FROM table1
WHERE
(Datepart(yyyy, dd) = 2017 And Datepart(mm, dd) In (1, 5, 12))
Or
(DatePart(yyyy, dd) = 2018 And DatePart(mm, dd) in (1))
延伸練習：查詢每一支電話每年每季的電話費總額
--Practice_每一支電話每年每季的電話費總額
select tel, y, q, sum(fee)
from (
select *, datepart(yyyy,dd) as y, datepart(qq,dd) as q
from bill
) as a
group by tel, y, q
思考方向：
由內往外整合資料，先確定目標是什麼。
產生年與季的欄位，以利後續group。
產生後，再查詢目標欄位(電話、年、電話費總額)
產生年與季的欄位，以利後續group。
產生後，再查詢目標欄位(電話、年、電話費總額)
提醒：巢狀查詢要由內往外看，才能知道查詢邏輯！
時間日期的加與減
GetDate() +/- n [目前時間 +/- n 天]GetDate() +/- n/24 [目前時間 +/- n小時]
--現在時間 -3 小時
GetDate() – 3.0/24.0 --現在時間 -2 天又 12 小時
GetDate() - 2.5
重點提醒：3.0/24.0 不能打成 3/24！
(因為在程式語言中，整數相除的意義是代表整數。故在程式語言中，3/24=0)
查詢某時段的資料**
範例：
dd Between GetDate() – 5.0/24.0 And GetDate()GetDate() – dd <= 5.0/24.02. 列出 3 天前的資料
dd Not Between GetDate() – 3 And GetDate()GetDate() – dd > 3其他函式補充 (for MS-SQL)
Day() 相當於 datepart(day, dd)Month() 相當於 datepart(month, dd)
Year() 相當於 datepart(year, dd)
留言
張貼留言