mysql按照日期方式查询

adsadadaddadasda

2020-04-19

查询显示时间，日期

SELECT t1,t2 FROM time_val;
SELECT dt FROM datetime_val;s
SELECT CURTIME(),CURTIME(2),CURTIME(6); (显示时间日期不同格式)
SELECT d,DATE_FORMAT(d,‘%M %d,%Y‘) FROM date_val;
SELECT d,DATE_FORMAT(d,‘%M,%d,%Y‘) AS date FROM date_val;
格式化字符含义
%Y 年份,四位数字
%y 年份,两位数字
%M 完整月份(January,Decemeber)
%b 月份,头三个字母(Jan..Dec)
%m 月份两位数字(01..12)
%c 月份(1..12)
%d 月份中天数,两位数字(01.31)
%e 月份中的天数(1..31)
%W 星期名字(Sunday-Saturday)
%r 时间,12小时制，以AM或PM结尾
%T 时间,24小时制
%H 小时,两位数字(00-23)
%i 分钟,两位数字(00..59)
%s 秒,两位数字(00.59)
%% 字母"%"
SELECT dt,DATE_FORMAT(dt,‘%c/%e/%y %r‘) AS format1,DATE_FORMAT(dt,‘%M %e,%Y %T‘) AS format2 FROM datetime_val;
SELECT dt,TIME_FORMAT(dt,‘%r‘) AS ‘12-hour time‘,TIME_FORMAT(dt,‘%T‘) AS ‘24-hour time‘ FROM datetime_val;

获取当前日期或时间

SELECT CURDATE(),CURTIME(),NOW();
INSERT INTO tsdemo(val) VALUES(5);
INSERT INTO tsdemo(val,ts_both,ts_create,ts_update) VALUES(10,NULL,NULL,NULL);
SELECT * FROM tsdemo;
SELECT dt,DATE(dt),TIME(dt) FROM datetime_val;
SELECT dt,YEAR(dt),DAYOFMONTH(dt),HOUR(dt),SECOND(dt) FROM datetime_val;
SELECT d,DAYOFYEAR(d) FROM date_val;
SELECT d,DAYNAME(d),LEFT(DAYNAME(d),3) FROM date_val;
SELECT dt,DATE_FORMAT(dt,‘%Y‘) AS year,DATE_FORMAT(dt,‘%d‘) AS day,TIME_FORMAT(dt,‘%H‘) AS hour,TIME_FORMAT(dt,‘%s‘) AS second FROM datetime_val;
SELECT dt,DATE_FORMAT(dt,‘%Y-%m-%d‘) AS ‘date part‘,TIME_FORMAT(dt,‘%T‘) AS ‘time part‘ FROM datetime_val;

合成日期或时间值

时间值和基本单位之间转换

SELECT t1,TIME_TO_SEC(t1) AS ‘TIME to seconds‘,SEC_TO_TIME(TIME_TO_SEC(t1)) AS ‘TIME to seconds to TIME‘ FROM time_val;
SELECT t1,TIME_TO_SEC(t1) AS ‘seconds‘,TIME_TO_SEC(t1)/60 AS ‘minutes‘,TIME_TO_SEC(t1)/(6060) AS ‘hours‘,TIME_TO_SEC(t1)/(2460*60) AS ‘days‘ FROM time_val;
ELECT dt,TIME_TO_SEC(dt) AS ‘time part in seconds‘,SEC_TO_TIME(TIME_TO_SEC(dt)) AS ‘time part as TIME‘ FROM datetime_val;
SELECT d,TO_DAYS(d) AS ‘DATE to days‘,FROM_DAYS(TO_DAYS(d)) AS ‘DATE to days to DATE‘ FROM date_val;
SELECT dt,TO_DAYS(dt) ‘date part in days‘,FROM_DAYS(TO_DAYS(dt)) AS ‘date part as DATE‘ FROM datetime_val;
SELECT dt,UNIX_TIMESTAMP(dt) AS seconds,FROM_UNIXTIME(UNIX_TIMESTAMP(dt)) AS timestamp FROM datetime_val;
SELECT dt,UNIX_TIMESTAMP(dt) AS seconds,FROM_UNIXTIME(UNIX_TIMESTAMP(dt)) AS timestamp FROM datetime_val;
SELECT DATEDIFF(@d1,@d2)AS ‘d1 - @d2‘,DATEDIFF(@d2,@d1) AS ‘d2-d1‘;
SELECT TIMEDIFF(@t1,@t2) AS ‘t1-t2‘,TIMEDIFF(@t2,@t1) AS ‘t2-t1‘;

时间，日期加减运算

SET @t1 = ‘12:00:00‘,@t2=‘15:30:00‘;
SELECT ADDTIME(@t1,@t2);
SET @dt = ‘1984-03-01 12:00:00‘,@t = ‘12:00:00‘;
SELECT ADDTIME(@dt,@t);
SELECT CURDATE(),DATE_ADD(CURDATE(),INTERVAL 3 DAY);
SELECT CURDATE(),DATE_ADD(CURDATE(),INTERVAL 1 WEEK);
SELECT NOW(),DATE_ADD(NOW(),INTERVAL ‘3 4‘ DAY_HOUR);
SELECT @d AS date,FROM_DAYS(TO_DAYS(@d) +7) AS ‘date + 1 week‘,FROM_DAYS(TO_DAYS(@d) - 7) AS ‘date -1 week‘;

计算年龄

SELECT * FROM sibling;
SELECT name,birth,CURDATE() AS today,TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS ‘age in years‘,TIMESTAMPDIFF(MONTH,birth,CURDATE()) AS ‘age in months‘ FROM sibling;
SELECT name,birth,‘1953-03-05‘ AS ‘Franz‘‘birth‘,TIMESTAMPDIFF(YEAR,birth,‘1953-03-05‘) AS ‘age in years‘,TIMESTAMPDIFF(MONTH,birth,‘1953-03-05‘) AS ‘age in months‘ FROM sibling WHERE name <> ‘Franz;

查询一个月第一天,最后一天或当月天数

SELECT d,DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY ) AS ‘1st of month‘ FROM date_val;
SELECT d,LAST_DAY(d) AS ‘last of month‘ FROM date_val;
SELECT d,LAST_DAY(DATE_ADD(d,INTERVAL -1 MONTH)) AS ‘last of previous month‘,LAST_DAY(DATE_ADD(d,INTERVAL 1 MONTH)) AS ‘last of following month‘ FROM date_val;
SELECT d,DAYOFMONTH(LAST_DAY(d)) AS ‘days in month‘ FROM date_val;

通过子字符串替换来计算日期

SELECT d,DATE_FORMAT(d,‘%y-%m-01‘) AS ‘1st of month A‘,CONCAT(YEAR(d),‘-‘,LPAD(MONTH(d),2,‘0‘),‘-01‘) AS ‘1st of month B‘ FROM date_val;
SELECT CURDATE(),DATE_ADD(DATE_FORMAT(CURDATE(),‘%Y-12-25‘),INTERVAL 2 YEAR) AS ‘Christmas A‘,DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 2 YEAR),‘%Y-12-25‘) AS ‘Christmas B‘;

计算星期

SELECT CURDATE(),DAYNAME(CURDATE());
SET @first = DATE_SUB(@d,INTERVAL DAYOFMONTH(@d)-1 DAY);
SELECT @d AS ‘starting date‘,@first as ‘1st of month date‘,DAYNAME(@first) AS ‘1st of month day‘;
SELECT d,DAYNAME(d) AS day,DATE_ADD(d,INTERVAL 1-DAYOFWEEK(d) DAY) AS Sunday,DATE_ADD(d,INTERVAL 7 -DAYOFWEEK(d) DAY) AS Saturday FROM date_val;
SET @target= DATE_SUB(DATE_ADD(CURDATE(),INTERVAL 4-DAYOFWEEK(CURDATE()) DAY),INTERVAL 14 DAY);
SELECT CURDATE(),@target,DAYNAME(@target);

计算润年

SELECT d,YEAR(d) % 4 = 0 AS ‘rule-of thumb test‘,(YEAR(d) %4 = 0) AND ((YEAR(d) % 100 <> 0) OR (YEAR(d) % 400 =0)) AS ‘complete test‘ FROM date_val;
SET @d1 = ‘2014-04-13‘,@d2=‘2016-04-13‘;
SELECT DAYOFYEAR(DATE_FORMAT(@d1,‘%Y-12-31‘)) AS ‘days in 2014‘,DAYOFYEAR(DATE_FORMAT(@d2,‘%Y-12-31‘)) AS ‘days in 2016‘;

非ISO格式日期字符串

SELECT d,CONCAT(YEAR(d),‘-‘,MONTH(d),‘-01‘) FROM date_val;
SELECT CONCAT(YEAR(d),‘-‘) AS ‘non-ISO‘,DATE_ADD(CONCAT(YEAR(d),‘-‘,MONTH(d),‘-01‘),INTERVAL 0 DAY) AS ‘ISO 1‘,CONCAT(YEAR(d),‘-‘,MONTH(d),‘-01‘) + INTERVAL 0 DAY AS ‘ISO 2‘,FROM_DAYS(TO_DAYS(CONCAT(YEAR(d),‘-‘,MONTH(d),‘-01‘))) AS ‘ISO 3‘,STR_TO_DATE(CONCAT(YEAR(d),‘-‘,MONTH(d),‘-01‘),‘%Y-%m-%d‘) AS ‘ISO 4‘ FROM date_val;

基于时间特征查询