整合營銷服務商
電腦端+手機端+微信端=數據同步管理
免費咨詢熱線：
電腦端+手機端+微信端=數據同步管理
免費咨詢熱線：
擊上方藍字關注我們
今天是SQL知識大全的第五講，主要內容是和時間函數相關，主要包括了常用的時間函數，時間提取函數，時間計算函數以及時間和時間戳之間的轉換。
常用的時間函數
to_date()函數
to_date()函數將字符串的日期轉換為時間格式的日期，轉換后的時間格式可以是如下形式：
格式
意義
YEAR 年的拼寫MM 數字月MONTH 月的全拼MON 月的縮寫DD 數字日DAY 星期的全拼DY 星期的縮寫AM 表示上午或者下午HH24、HH12 12小時制或24小時制?MI 分鐘??SS 秒鐘???
具體示例如下：
SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI') FROM DUAL
SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24') FROM DUAL
SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD') FROM DUAL
SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL
SELECT TO_DATE('2006', 'YYYY') FROM DUALcurrent_date()/current_time()
返回當前時間,某年某月某日，current_time()等同于current_date。
SELECT CURDATE()# 返回值：
# '2020-10-09'?current_timestamp()
current_timestamp()相當于now(),返回當前的時間戳。
時間提取
時間提取函數包括year(),month(),day(),hour(),minute(),second()等，其用法都是一直的，這些函數分別返回 date 的年份，月份，日期，時間等，范圍為1000-9999。當 date 為0時，返回0。
具體示例如下：
SELECT YEAR('98-02-03')
# 返回值 1998
SELECT MONTH('98-02-03')
# 返回值 02
SELECT DAY('98-02-03')
# 返回值 3時間計算函數
DATEDIFF()
語法：DATEDIFF(expr1,expr2)
DATEDIFF()將返回expr1 - expr2的值，用來表示兩個日期相差的天數。expr1 和 expr2 都是日期或日期時間表達式。運算中只用到了這些值的日期部分。
SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30')
#返回結果
1date_sub()/date_add()
語法格式：
DATE_ADD(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit)
執行日期計算的兩種函數。date 是一個用來指定開始日期的 DATETIME 或 DATE 值。expr 是一種以字符串形式呈現的表達式，用來指定從開始日期增加或減少的間隔值。如果是負的間隔值，則 expr 值的第一個字符是-號。unit 是一個單位關鍵字，用來指定expr表達式應該采取的單位。
INTERVAL 關鍵字與單位說明符都不區分大小寫。
下表列出了每個單位數值所對應的 expr 參數的期望格式。
單位所能取的值期望的expr格式MICROSECOND毫秒SECOND秒MINUTE分HOUR小時DAY日WEEK周MONTH月QUARTER季度YEAR年SECOND_MICROSECOND'秒.毫秒'MINUTE_MICROSECOND'分.毫秒'MINUTE_SECOND'分:秒'HOUR_MICROSECOND'小時.毫秒'HOUR_SECOND'小時:分:秒'HOUR_MINUTE'小時:分'DAY_MICROSECOND'日.毫秒'DAY_SECOND'日 小時:分:秒'DAY_MINUTE'日 小時:分'DAY_HOUR'日 小時'YEAR_MONTH'年-月'
時間戳
時間戳的定義
時間戳是從1970年1月1日（UTC/GMT的午夜）開始所經過的秒數，不考慮閏秒。
UNIX時間戳的0按照ISO 8601規范為 ：1970-01-01T00:00:00Z.
一個小時表示為UNIX時間戳格式為：3600秒；一天表示為UNIX時間戳為86400秒，閏秒不計算。
在大多數的UNIX系統中UNIX時間戳存儲為32位，這樣會引發2038年問題或Y2038。
UNIX_TIMESTAMP(date)
若無參數調用，則返回一個 Unix timestamp ('1970-01-01 00:00:00' GMT 之后的秒數) 作為無符號整數，得到當前時間戳 。
若用date 來調用 UNIX_TIMESTAMP()，它會將參數值以'1970-01-01 00:00:00' GMT后的秒數的形式返回。date 可以是一個 DATE 字符串、一個 DATETIME字符串、一個 TIMESTAMP或一個當地時間的YYMMDD或YYYMMDD格式的數字。
例如:
SELECT UNIX_TIMESTAMP() ; （執行時的時間:2009-08-06 10:10:40）
# 1249524739
SELECT UNIX_TIMESTAMP('2009-08-06') ;
# 1249488000from_unixtime(）
from_unixtime(t1,’yyyy-MM-dd HH:mm:ss’)
其中t1是10位的時間戳值，即1970-1-1至今的秒，而13位的所謂毫秒的是不可以的。
對于13位時間戳，需要截取，然后轉換成bigint類型，因為from_unixtime類第一個參數只接受bigint類型。例如：
select from_unixtime(cast(substring(tistmp,1,10) as bigint),’yyyy-MM-dd HH’) tim ,count(*) cn from ttengine_hour_data
where …
SELECT from_unixtime(cast(ts AS bigint))
FROM temptable參考文章
https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html
https://baike.baidu.com/item/unix%E6%97%B6%E9%97%B4%E6%88%B3
https://blog.csdn.net/qq_24309787/article/details/81095238
SQL系列文章持續更新中
往期推薦
史上最全的SQL知識點匯總，錯過這次在等一年
SQL知識大全(一):數據庫的語言分類你都知道嗎？
SQL知識大全(二):SQL的基礎知識你都掌握了嗎？
SQL知識大全三):SQL中的字符串處理和條件查詢
SQL知識大全（四):SQL中的數據表關聯
分享數據知識，成就數據理想
select id, phone,time,year(time),month(time), DAY(time),TIME(time) from user where phone='xxxxxx' #分別取年、月、日、時間
select id, phone,time from user where phone='xxxxxx' #
select id, phone,time,year(time) from user where phone='xxxxxx' #分別取年
select id, phone,time,month(time) from user where phone='xxxxxx' #分別取月
select id, phone,time,DAY(time) from user where phone='xxxxxx' #分別取日
select id, phone,time,TIME(time) from user where phone='xxxxxx' #分別取時間
言：學習python第3天
需求：簡單的事件提醒功能
版本：python3.9、mysql5.7
1、現在mysql建一個表event_remind
CREATE TABLE `event_remind` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
`project_name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '所屬項目',
`type` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '類型：domain/SSL/',
`title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '事項',
`expire_date` datetime DEFAULT NULL COMMENT '到期時間',
`ding_talk_group` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '釘釘群',
`ding_talk_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '釘釘token',
`remark` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '備注',
`send_num` int(11) DEFAULT NULL COMMENT '發送計數',
`send_last_time` datetime DEFAULT NULL COMMENT '最近發送時間',
`status` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '狀態',
`test_flag` tinyint(4) DEFAULT NULL COMMENT '測試',
`period` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '周期（common=普通，every_month=每月)',
`repeat_interval` int(11) DEFAULT NULL COMMENT '重復周期(結合period的值，1=1號）',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='運維事項提醒';關鍵查詢到期事件sql
# 查詢大于7天且30天內的的事件在周日提醒一次， 小于7天的事件則每天提醒一次。
select
id,
project_name,
title,
expire_date,
date_format(expire_date, '%Y-%m-%d') expire_date_format,
status,
ding_talk_token,
DATEDIFF(expire_date,now()) diff_days,
test_flag,
period,
repeat_interval,
# 類型為common默認提醒，every_month類型需匹配repeat_interval日期才提醒，1提醒，-1不提醒
(case when period = 'common' then 1
when period = 'every_month' and repeat_interval = DATE_FORMAT(now(),'%e') then 1
else -1 end) match_flag
from np_event_remind
where status = 'ON'
and (case
when DATEDIFF(expire_date,now()) between 0 and 5 then 1
when DATEDIFF(expire_date,now()) between 6 and 30 and WEEKDAY(now()) = 6 then 1
else -1 end) = 1
and (case when period = 'common' then 1
when period = 'every_month' and repeat_interval = DATE_FORMAT(now(),'%e') then 1
else -1 end) = 1 ;使用到的函數
# 星期函數
select
WEEK(now()) as '一年的第幾周',
WEEKDAY(now()) as '星期幾(0=星期一)',
MONTH(now()) as '一年的第幾月份',
DATE_FORMAT(now(),'%e') as '一個月的幾號';
# 日期增加一個月
select adddate(now(), interval 1 month);2、編寫python腳本，event_remind.py文件
# -*- coding: utf-8 -*-
import datetime
import json
import pymysql.cursors
import requests
"""
功能說明：到期事件釘釘提醒功能
作者：CF
"""
connect = pymysql.Connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='',
db='',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
# 返回datetime格式：eg：2019-12-07 20:38:35.82816, 格式化：now.strftime('%Y-%m-%d')
now = datetime.datetime.now()
cursor = connect.cursor()
# 入口函數
def main():
# 查詢30天內即將到期的事項
# 30天內且周日提醒，7天內每天提醒
sql = '''
select
id,
project_name,
title,
expire_date,
date_format(expire_date, '%Y-%m-%d') expire_date_format,
status,
ding_talk_token,
DATEDIFF(expire_date,now()) diff_days,
test_flag,
period,
repeat_interval
from event_remind
where status = 'ON'
and (case
when DATEDIFF(expire_date,now()) between 0 and 5 then 1
when DATEDIFF(expire_date,now()) between 6 and 30 and WEEKDAY(now()) = 6 then 1
else -1 end) = 1
and (case when period = 'common' then 1
when period = 'every_month' and repeat_interval = DATE_FORMAT(now(),'%e') then 1
else -1 end) = 1
'''
# print("查詢數據SQL：%s" % sql)
cursor.execute(sql)
result = cursor.fetchall()
for x, event_remind in enumerate(result):
print('{} - 序號: {}, id: {}, 事項: {}, 剩余日期：{}'.format(datetime.datetime.now(), x, event_remind['id'],
event_remind['title'],
event_remind['diff_days']))
# 處理發送通知
# period：common默認發送，every_month周期發
notice(event_remind)
# 關閉數據庫連接
connect.close()
def notice(event_remind):
content = ""
# 測試
if event_remind['test_flag'] == 1:
content = "通知測試"
# 按周期
elif event_remind['period'] == 'every_month':
content = "{}-{}".format(event_remind['project_name'], event_remind['title'])
# 默認
elif event_remind['period'] == 'common':
if event_remind['diff_days'] == 0:
content = "{}-{}，今日到期。".format(event_remind['project_name'], event_remind['title'])
elif event_remind['diff_days'] > 0:
content = "{}-{}，到期日：{}，剩余時間：{}天。".format(event_remind['project_name'], event_remind['title'],
event_remind['expire_date_format'],
event_remind['diff_days'])
# print(content)
# 發送釘釘通知
ding_talk_notice(event_remind['ding_talk_token'], content)
# 記錄發送，標記發送次數
cursor.execute("update event_remind set send_num = send_num +1, send_last_time=now() where id ={}".format(
event_remind['id']))
cursor.execute("commit")
# 釘釘消息關鍵詞發送(關鍵詞10個)
# 官方文檔：https://open.dingtalk.com/document/robots/custom-robot-access
def ding_talk_notice(dingding_access_token, content):
if dingding_access_token == '':
print("{}-發送釘釘消息異常，token為空".format(datetime.datetime.now()))
return
# print("測試釘釘發送消息-關鍵詞方式或IP方式")
# 釘釘機器人Webhook地址
webhook_url = f"https://oapi.dingtalk.com/robot/send?access_token={dingding_access_token}"
# 要發送的消息內容 （強制atAll,需要在釘釘端設置除管理員外都可以）
message = {
"msgtype": "text",
"text": {
"content": "[到期提醒]{}".format(content)
},
"at": {
"isAtAll": True
}
}
# 頭信息，需要包含
headers = {
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.45 Safari/537.36',
'Connection': 'keep-alive',
'Content-Type': 'application/json',
'Accept': 'application/json',
}
# 將消息轉換為JSON格式并發送到釘釘機器人
response = requests.post(webhook_url, data=json.dumps(message), headers=headers)
if response.status_code != 200:
print('發送異常,狀態碼:{}'.format(response.status_code))
print('發送異常,返回內容: {}'.format(response.text))
if __name__ == '__main__':
main()
3、設置crontab定時每天8點半執行
crontab -e
30 8 * * * /usr/bin/python3 /home/script/event_remind.py 2>&1 >> /var/log/script/event_remind.log4、釘釘提醒
配置一個釘釘機器人， 用簡單的關鍵詞驗證模式即可，比如配置一個提醒。 然后配置下webhook即可發送釘釘消息
webhook
釘釘提醒
*請認真填寫需求信息，我們會在24小時內與您取得聯系。
