数据库篇-06-sql -1.实战整理mysql常用语法大全

***#1.查询时间类型转日期类型：

（dateTime转date）：DATE_FORMAT(‘列名’,’%Y-%m-%d’)**

***#2.获取两个时间差

(1.dateTime先转成秒值再做差：UNIX_TIMESTAMP(statistic_end_time
2.再做差：UNIX_TIMESTAMP(statistic_end_time) - UNIX_TIMESTAMP(statistic_start_time) AS taskDuration)**

***#3.根据开始时间-statistic_start_time字段（dateTime类型）获取今日和昨日和前日的数据

(1.先转成date类型或to_days类型：
DATE_FORMAT(statistic_start_time,’%Y-%m-%d’) =
以及
to_days(statistic_start_time)=

2.再获取昨日：
DATE_FORMAT(statistic_start_time,’%Y-%m-%d’) =date_add(curdate(), INTERVAL - 1 DAY)

3.和今日：
to_days(statistic_start_time) = to_days(now()))

4.和前日：
DATE_FORMAT(statistic_start_time,’%Y-%m-%d’) = date_add(curdate(), INTERVAL - 2 DAY)**

//合并1.2.3.需求MySql如下：根据任务时长倒叙获取前10个任务记录

实现方式一：

SELECT
	NAME AS taskNmae,
	DATE_FORMAT(
		statistic_start_time,
		'%Y-%m-%d'
	) AS startTime,
	UNIX_TIMESTAMP(statistic_end_time) - UNIX_TIMESTAMP(statistic_start_time) AS taskDuration
FROM
	hera_action
WHERE
	DATE_FORMAT(
		statistic_start_time,
		'%Y-%m-%d'
	) = date_add(curdate(), INTERVAL - 1 DAY)
OR to_days(statistic_start_time) = to_days(now())
order by taskDuration desc limit 10

实现方式二：

SELECT
	name as taskNmae,
	(case when to_days(statistic_start_time) = to_days(now()) then  UNIX_TIMESTAMP(statistic_end_time)-UNIX_TIMESTAMP(statistic_start_time)  else 0 end) as todayHours,
	(case when DATE_FORMAT(
		statistic_start_time,
		'%Y-%m-%d'
	) = date_add(curdate(), INTERVAL - 1 DAY) then
	UNIX_TIMESTAMP(statistic_end_time)-UNIX_TIMESTAMP(statistic_start_time)  else 0 end) as yesterdayHours
	/*DATE_FORMAT(
		statistic_start_time,
		'%Y-%m-%d'
	) as startTime,  UNIX_TIMESTAMP(statistic_end_time)-UNIX_TIMESTAMP(statistic_start_time)  as taskDuration*/
FROM
	hera_action
WHERE
	1=1
order by yesterdayHours desc