项目小锦囊——mysql时间篇

mysql时间

计算两个时间段的时长

TIMESTAMPDIFF(MINUTE,开始时间,结束时间)

MINUTE: 参数是确定的结果的单位 分钟

他的参数有：
MICROSECOND，SECOND，MINUTE，HOUR，DAY，WEEK，MONTH，QUARTER，YEAR

TIMESTAMPDIFF(MINUTE,开始时间,结束时间) AS time_length
//注意 如果时间相减没有一天会为0 就需要格式化
TIMESTAMPDIFF(MINUTE,DATE_FORMAT(开始时间,'%Y-%m-%d' ),DATE_FORMAT(结束时间,'%Y-%m-%d' ))

修改时间格式

DATE_FORMAT(时间,格式)
他的格式有：’%Y-%m-%d’ 年-月-日 ，’%Y-%m-%d %H:%i:%%s’ 年-月-日 时:分:秒

DATE_FORMAT(时间,'%Y-%m-%d') `date`

字符串拼接

CONCAT_WS(separator, str1, str2, …) 和 CONCAT( str1, str2, …) 都可以字符串拼接

separator是用来指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。

CONCAT_WS('',province.`name`,city.`name`,area.`name`,dr.`name`,drc.`name`,d.address)  address

最大值和最小值

GREATEST(value1, value2, …);
LEAST(N1,N2,N3,N4,…)

SELECT GREATEST(10, 20, 30),  -- 30
       LEAST(10, 20, 30); -- 10

经纬度计算距离

113.58301 这是用户1 的经度，23.30943 这是用户1的纬度
c.longitude 这是用户2 的经度，c.latitude 这是用户2的纬度

(2 * 6378.137 * ASIN(	SQRT(POW( SIN( PI( ) * ( 113.58301 - c.longitude ) / 360 ), 2 ) + COS( PI( ) * 23.30943/ 180 ) * COS(  c.latitude * PI( ) / 180 ) * POW( SIN( PI( ) * ( 23.30943- c.latitude ) / 360 ), 2 )))) AS distance

添加/减 天数

CURDATE() 获取当前的年月日
DATE_ADD(CURDATE(), INTERVAL 3 DAY) 在当前日期加3天

DATE_SUB(CURDATE(), INTERVAL 7 DAY) 当前日期减7天

select DATE_ADD(CURDATE(), INTERVAL 3 DAY)

select DATE_SUB(CURDATE(), INTERVAL 7 DAY)

日期加减

DATEDIFF(datepart , startdate , enddate)

datepare值：year | quarter | month | week | day | hour | minute | second | millisecond
startdate：开始日期
enddate ：结束日期

下面例子中表名为tablename,条件字段名为inputdate

查询今天

SELECT * FROM tablename where DATEDIFF(day,inputdate,GETDATE())=0

查询昨天

SELECT * FROM tablename where DATEDIFF(day,inputdate,GETDATE())=1

查询本周

SELECT * FROM tablename where DATEDIFF(week,inputdate,GETDATE())=0

查询上周

SELECT * FROM tablename where DATEDIFF(week,inputdate,GETDATE())=1

查询本月

SELECT * FROM tablename where DATEDIFF(month,inputdate,GETDATE())=0

查询上月

SELECT * FROM tablename where DATEDIFF(month,inputdate,GETDATE())=1