前言:Oracle、MySQL、Postgresql、SqlServer中对于时间的处理是不同的,为了便于使用和区分,我在来列一下这常见的四种数据库中对于时间进行处理方式。
目录
3.4 查看现在距1970-01-01 00:00:00 UTC 的秒数
一、Oracle
查看当前日期格式:select * from nls_session_parameters where parameter='NLS_DATE_FORMAT';
修改日期的格式: alter session set nls_date_format = 'yyyy-mm-dd';
永久设置日期格式:改注册表oracle/HOME0 加字符串NLS_DATE_FORMAT 值yyyy-mm-dd;
1.dd-mon-yy转换为yyyy-mm-dd
select to_char(to_date('01-5月-05','dd-mon-yy'),'yyyy-mm-dd') from dual
2.计算2009-05-01与2008-04-30的月份差
select (extract(year from to_date('2009-05-01 ','yyyy-mm-dd')) - extract(year from to_date('2008-04-30','yyyy-mm-dd'))) * 12 +
extract(month from to_date('2008-05-01','yyyy-mm-dd')) - extract(month from to_date('2008-04-30','yyyy-mm-dd')) months from dual;
--extract:用于从日期时间值中取得所需要的特定数据 year.month.day.hour.minute.second
select ceil((to_date('2009-05-01','yyyy-mm-dd') - to_date('2008-04-30','yyyy-mm-dd'))/30) from dual;
select ceil(months_between(to_date('2009-05-01','yyyy-mm-dd'),to_date('2008-04-30','yyyy-mm-dd'))) from dual;
--查看现在距2008-08-08已过去了多少个月
select ceil(months_between(sysdate,to_date('2008-08-08','yyyy-mm-dd'))) from dual;
3.年月日时分秒计算
select to_date('2010-04-27 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual; --字符串转换成日期
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; --日期转化为字符串
select to_char(sysdate,'yyyy') as nowYear from dual; --获取时间的年 'yyyy.yyy.yy.y'分别显示不同年的计数位
--获取时间的月
select to_char(sysdate,'mm') from dual; -- 04
select to_char(sysdate,'mon') from dual; --中文版:4月 英文版: apr
select to_char(sysdate,'month') from dual; --中文版:4月 英文版: april
--获取时间的日
select to_char(sysdate,'dd') from dual; --当月的第几天
select to_char(sysdate,'ddd') from dual; --当年的第几天
select to_char(sysdate,'d') from dual; --当周的第几天 select to_char(sysdate,'D') from dual;
select to_char(sysdate,'ddspth')from dual; --英文显示当月的第几天
--获取时间的时
select to_char(sysdate,'hh24') from dual; --24小时制
select to_char(sysdate,'hh') from dual; --12小时制
--获取时间的分
select to_char(sysdate,'mi') from dual;
--获取时间的秒
select to_char(sysdate,'ss') from dual;
4.查看星期几
select to_char(sysdate,'dy') from dual; --星期几 中文版:星期二 英文版:Tue
select to_char(sysdate,'day') from dual; --星期几 中文版:星期二 英文版:Tuesday
--英文显示星期几: to_date('2010-04-27','yyyy-mm-dd') 部分可换成sysdate或其他时间
select to_char(to_date('2010-04-27','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual; --小写
select to_char(to_date('2010-04-27','yyyy-mm-dd'),'DAY','NLS_DATE_LANGUAGE = American') from dual; --大写
select to_char(to_date('2010-04-27','yyyy-mm-dd'),'Day','NLS_DATE_LANGUAGE = American') from dual; --首字母大写
--设置会话日期语言格式 : alter session set nls_date_language='american';
5. next_day函数:计算机当前日期的下一个星期几
next_day(sysdate,6)是从当前开始下一个星期五(n-1)。后面的数字是从星期日开始算起。
1 2 3 4 5 6 7
日 一 二 三 四 五 六
--本月的第一个星期一
select next_day(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'),'星期一') from dual;
select next_day(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'),2) from dual
--计算上一个月的第一个星期一
select next_day(add_months(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'),-1),'星期一') from dual;
select next_day(add_months(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'),-1),2) from dual;
--计算下一个月的第一个星期一
select next_day(add_months(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'),1),'星期一') from dual;
select next_day(add_months(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'),1),2) from dual;
6.判断一年是不是闰年
--如果是28-平年, 29-闰年
select to_char( last_day( to_date('02' ||&year,'mmyyyy') ), 'dd' ) from dual;
select to_char( last_day( to_date('02' ||extract(year from sysdate),'mmyyyy') ), 'dd' ) from dual;
select to_char(last_day(add_months(trunc(sysdate,'year'),2)-1),'dd') from dual;
select decode(to_char(last_day(to_date(to_char(sysdate,'yyyy')||'-02-01','yyyy-mm-dd')),'dd'),'28','平年','闰年') from dual
7.计算今年的天数:
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
8.计算本月的第一天
select to_char(last_day(add_months(sysdate,-1))+1,'yyyy-mm-dd') from dual;
select to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd') from dual;
9.计算本月最后一天
--select last_day(sysdate)from dual;
select last_day(trunc(sysdate)) from dual;
select trunc(last_day(add_months(sysdate,0))) from dual;
select add_months(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd'),1)-1 from dual;
select last_day(to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd')) from dual;
select add_months(to_date(to_char(last_day(add_months(sysdate,-1))+1,'yyyy-mm-dd'),'yyyy-mm-dd'),1)-1 from dual;
10.上个月最后一天
select trunc(last_day(add_months(sysdate,-1))) from dual;
select to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd')-1 from dual;
--select trunc(last_day(add_months(sysdate,-1)))+1-1/24/60/60 from dual
11.last_day(trunc(sysdate))扩展
--本月第一天
select add_months(last_day(trunc(sysdate)),-1)+1 from dual;
--上个月第一天
select add_months(last_day(trunc(sysdate)),-2)+1 from dual;
--下个月第一天
select add_months(last_day(trunc(sysdate)),0)+1 from dual;
--本月最后一天
select last_day(trunc(sysdate))from dual;
--上个月最后一天
select add_months(last_day(trunc(sysdate)),-1) from dual;
--下个月最后一天
select add_months(last_day(trunc(sysdate)),1) from dual;
--计算本月的天数
select last_day(trunc(sysdate)) - (add_months(last_day(trunc(sysdate)),-1)+1)+1 from dual;
--select trunc(last_day(sysdate))- to_date(to_char(sysdate,'yyyy-mm')||'-01','yyyy-mm-dd')+1 from dual
--计算上个月的天数
select add_months(last_day(trunc(sysdate)),-1) - (add_months(last_day(trunc(sysdate)),-2)+1)+1 from dual;
--计算下个月的天数
select add_months(last_day(trunc(sysdate)),1) - (add_months(last_day(trunc(sysdate)),0)+1)+1 from dual;
12.trunc(sysdate,'month') 扩展
--本月的第一天
select trunc(sysdate,'month') from dual;
--本月最后一天
select last_day(trunc(sysdate, 'month')) from dual;
--上个月的第一天
select trunc(trunc(sysdate, 'month') - 1, 'month') from dual;
--上个月的最后一天
select trunc(sysdate, 'month') - 1 from dual;
--下个月的第一天
select last_day(trunc(sysdate, 'month'))+1 from dual;
--下个月的最后一天
select last_day(last_day(trunc(sysdate, 'month'))+1)from dual;
13.计算本年的第一天
select trunc(sysdate,'year') from dual;
select trunc(sysdate,'yyyy') from dual;
select to_date(to_char(sysdate,'yyyy')||'-01-01','yyyy-mm-dd') from dual;
14.计算本年的最后一天
select add_months(trunc(sysdate,'year'),12)-1 from dual;
select to_date(to_char(sysdate,'yyyy')||'-12-31','yyyy-mm-dd') from dual;
select add_months(to_date(to_char(sysdate,'yyyy')||'-01-01','yyyy-mm-dd'),12)-1 from dual;
select add_months(last_day(to_date(to_char(sysdate,'yyyy')||'-01-01','yyyy-mm-dd')),11) from dual;
15.计算两个日期间的星期几的总数
--计算2010-04-28至2010-10-01之间的工作日的总数
select count(*)
from (select rownum-1 rnum
from all_objects
where rownum <= to_date('2010-10-01','yyyy-mm-dd') - to_date('2010-04-28','yyyy-mm-dd')+1
)
where to_char( to_date('2010-04-28','yyyy-mm-dd')+rnum, 'day')not in ( '星期六', '星期日' );
--替换说明: all_objects --> dual; where rownum --> connect by rownum/level; day --> d/D, dy
--"星期六"、"星期日" --> 1,7 或单个数字 'sat'.'sun'; not in --> in; count(*) --> * ; 日期可替换成替他形式.
16.查看当前系统时间的四种方法
select sysdate from dual;
select current_date from dual;
select add_months(sysdate, 0) from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
17.计算本周星期几的日期
--1-7:oracle默认一周从星期日开始
select trunc(sysdate,'day')+1 from dual;
select trunc(sysdate,'day')+2 from dual;
select trunc(sysdate,'day')+3 from dual;
select trunc(sysdate,'day')+4 from dual;
select trunc(sysdate,'day')+5 from dual;
select trunc(sysdate,'day')+6 from dual;
select trunc(sysdate,'day')+7 from dual;
--0-6:iso默认一周从星期一开始
select trunc(sysdate,'iw')+0 from dual;
select trunc(sysdate,'iw')+1 from dual;
select trunc(sysdate,'iw')+2 from dual;
select trunc(sysdate,'iw')+3 from dual;
select trunc(sysdate,'iw')+4 from dual;
select trunc(sysdate,'iw')+5 from dual;
select trunc(sysdate,'iw')+6 from dual;
18.如何求一年的总天数
把下一年的1.1减当年的1.1
select add_months(trunc(sysdate,'yyyy'),12)-trunc(sysdate,'yyyy') from dual
select case when (mod(to_number(to_char(sysdate,'YYYY')),4) =0 or mod(to_number(to_char(sysdate,'YYYY')),100 ) =0)
and mod(to_number(to_char(sysdate,'YYYY')),400 ) <>0
then 366 else 365
end total_days
from dual
19.计算奥运会距离现在的时间
select '奥运会距今已过:' || trunc(dt) || '天' ||
trunc((dt - trunc(dt)) * 24) || '小时' ||
trunc((dt * 24 - trunc(dt * 24)) * 60) || '分' ||
trunc((dt * 1440 - trunc(dt * 1440)) *60) || '秒'
from(
select sysdate - to_date('2008-08-08 20:00:00', 'yyyy-mm-dd hh24:mi:ss') dt
from dual
);
select extract(day from dt) "天",
extract(hour from dt) "小时",
extract(minute from dt) "分",
trunc(extract(second from dt)) "秒"
from (
select systimestamp - to_date('2008-08-08 20:00:00', 'yyyy-mm-dd hh24:mi:ss') dt
from dual
);
附:
1. 时间格式范围
hh24:24小时格式下时间范围为: 0:00:00 - 23:59:59
hh12:12小时格式下时间范围为: 1:00:00 - 12:59:59
2.时间为null的情况
select to_date(null) from dual;
select to_char(null) from dual;
3.时间格式的设置
--系统设置
alter system set NLS_DATE_LANGUAGE = American
--会话设置
alter session set NLS_DATE_LANGUAGE = American
3.计算一个季度的总天数
select last_day(to_date(to_char(sysdate,'yyyy-')||lpad(floor(to_number(to_char(sysdate,'mm'))/3)*3+3,2,'0')||'-01','yyyy-mm-dd'))
-to_date(to_char(sysdate,'yyyy-')||lpad(floor(to_number(to_char(sysdate,'mm'))/3)*3+1,2,'0')||'-01','yyyy-mm-dd')
+1
from dual;
4.判断当前时间是上午还是?
select case when to_number(to_char(sysdate,'hh24')) between 6 and 11 then '上午'
when to_number(to_char(sysdate,'hh24')) between 12 and 18 then '下午'
else '晚上'
end
from dual;
select case when to_number(to_char(sysdate,'hh24')) between 1 and 5 then '凌晨'
when to_number(to_char(sysdate,'hh24')) between 6 and 11 then '上午'
when to_number(to_char(sysdate,'hh24')) between 12 and 18 then '下午'
when to_number(to_char(sysdate,'hh24')) between 19 and 24 then '晚上'
end
from dual;
5.查找月份差:months_between(date,date)
select months_between(to_date('01-31-2010','mm-dd-yyyy'),to_date('12-31-2009','mm-dd-yyyy')) "months" from dual;
6.查看某一天距今天的天数:select trunc(sysdate)-to_date('20050101','yyyymmdd') from dual;
7. 一年以后的今天: select add_months(sysdate,12) from dual;
一年以前的今天: select add_months(sysdate,-12) from dual;
系统当前时间: select add_months(sysdate, 0) from dual;
8.从今天零点之后经过的秒数:select to_char(sysdate,'SSSSS') from dual;
9.本周星期几(n)的日期: select trunc(sysdate,'day') + n from dual;
10.本月的天数:select to_char(last_day(sysdate),'dd') days from dual
11.计算当前月所有星期五的日期
select to_char(dt,'yyyy-mm-dd')
from(select trunc(sysdate,'mm')+rownum-1 dt
from dual connect by rownum<=31
)t
where to_char(t.dt,'mm') = to_char(sysdate,'mm')
and to_char(t.dt,'day')= '星期五';
--计算当前月所有星期五的日期(以","隔开)
select wm_concat(to_char(dt,'yyyy-mm-dd'))
from(select trunc(sysdate,'mm')+rownum-1 dt from dual connect by rownum<=31)t
where to_char(t.dt,'mm') = to_char(sysdate,'mm')
and to_char(t.dt,'day')= '星期五';
--计算当前月之前3个月的所有星期五的日期
select to_char(dt,'yyyy-mm-dd')
from(select trunc(sysdate,'mm')+rownum-1 dt from dual connect by rownum<= (select add_months(sysdate,3)-trunc(sysdate)))t
where to_char(t.dt,'day')= '星期五';
二、MySQL
1、时间与字符串转换
DATE_FORMAT
和TIME_FORMAT
日期时间转换函数可以将一个日期或时间转换成各种字符串格式。DATE_FORMAT
和TIME_FORMAT
实际是STR_TO_DATE
函数的逆转换
1.1、STR_TO_DATE 字符串转时间
STR_TO_DATE
转换函数可以将杂乱无章的字符串转换为日期格式,同时也可以用来转换时间。
STR_TO_DATE(str, format)
例如:
SELECT STR_TO_DATE('2019-05-14', '%Y-%m-%d %H:%i:%s')
-- 2019-05-14 00:00:00
1.2、DATE_FORMAT 日期格式化
DATE_FORMAT(date, format)
例如:
SELECT DATE_FORMAT(NOW(), "%Y-%m-%d %H:%i:%s")
-- 2019-05-14 16:04:20
1.3、TIME_FORMAT 时间格式化
TIME_FORMAT(time, format)
例如:
SELECT TIME_FORMAT(NOW(),'%H:%i:%s')
-- 17:24:44
1.4、日期时间格式化字符串
日期格式化取值范围
年
%Y
四位数字表示的年份,如2019...%y
两位数字表示的年份,如19...
月
%M
英文表示的月份,如January、February...%b
英文缩写表示的月份,如Jan,Feb...%m
两位数字表示的月份,如01,02,03...%c
数字表示的月份,如1,2,3...
日
%d
两位数字表示的月中天数,如 01, 02...%e
数字表示的月中天数,如1,2...%D
英文后缀表示的月中天数,如1st, 2st...%j
以三位数字表示的年中天数,如001, 002...
周
%W
英文全程表示的一周中每一天的名称,如Sunday, Monday...%a
英文缩写表示的一周中每一天的名称,如Sun, Mon...%w
数字形式表示一周中每一天,周日为0,周一为1,依次递增。%U
数字形式表示的周数,周日为第一天。%u
数字形式表示的周数,周一为第一天。
小时
%H
24小时制两位数形式表示的小时,如00, 01,...,23%h
12小时制两位数形式表示的小时,如01, 02,...,12%k
24小时制数字形式表示的小时,如0,1,2,...,23%l
12小时制数字形式表示的小时,如0,1,2,...,12%T
24小时制的时间形式HH:mm:ss
%r
12小时制时间形式hh:mm:ss AM/PM
%p
AM
上午或PM
下午
分钟
%i
两位数字形式表示的分钟,如00,01,02,...,59%I
秒数
%S
两位数字形式表示的秒数,如00,01,02,...,59%s
两位数字形式表示的秒数,如00,01,02,...,59
2、时间戳转换
2.1 UNIX_TIMESTAMP
UNIX_TIMESTAMP()
获取当前时间戳
SELECT UNIX_TIMESTAMP()
-- 1557826503
UNIX_TIMESTAMP(date)
时间转时间戳
SELECT UNIX_TIMESTAMP(NOW())
-- 1557825088
UNIX_TIMESTAMP(str)
字符串转时间戳
例如:将字符串的日期转换为时间戳
SELECT UNIX_TIMESTAMP('2019-05-14')
-- 1557763200
2.2 FROM_UNIXTIME
FROM_UNIXTIME(unix_timestamp)
时间戳转字符串
SELECT FROM_UNIXTIME(1557763200)
-- 2019-05-14 00:00:00
FROM_UNIXTIME(unix_timestamp, format)
时间戳转字符串
SELECT FROM_UNIXTIME(1557763200, '%Y-%m-%d %H:%i:%s')
-- 2019-05-14 00:00:00
3、 拼凑日期时间
MAKEDATE(year, dayofyear)
MAKETIME(hour, minute, second)
4、 天数转换
TO_DAYS(date)
日期转换为天数FROM_DAYS(days)
天数转换为日期
5、日期计算
5.1 DATE_SUB
DATE_SUB(date,INTERVAL expr unit)
当前日期时间
-- 2019-05-14 18:07:33
SELECT NOW()
计算年份
-- 今年 2019
YEAR(NOW())
-- 去年,前一年 2018
YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR))
-- 明年,后一年 2020
YEAR(DATE_SUB(NOW(), INTERVAL -1 YEAR))
计算月份
-- 当前月 5
MONTH(NOW())
-- 上个月 4
MONTH(DATE_SUB(NOW(),INTERVAL 1 MONTH))
-- 下个月 6
MONTH(DATE_SUB(NOW(),INTERVAL -1 MONTH))
计算天数
-- 月中天数 14
DAY(DATE_SUB(NOW(), INTERVAL 0 DAY))
-- 昨天:前一天 13
DAY(DATE_SUB(NOW(), INTERVAL 1 DAY))
-- 明天:后一天 15
DAY(DATE_SUB(NOW(), INTERVAL -1 DAY))
计算小时
-- 当前小时数 18
HOUR(DATE_SUB(NOW(), INTERVAL 0 HOUR))
-- 前一小时 17
HOUR(DATE_SUB(NOW(), INTERVAL 1 HOUR))
-- 后一小时 19
HOUR(DATE_SUB(NOW(), INTERVAL -1 HOUR))
计算分钟
-- 当前分钟数
MINUTE(DATE_SUB(NOW(), INTERVAL 0 MINUTE))
-- 30分钟前的分钟数
MINUTE(DATE_SUB(NOW(), INTERVAL 30 MINUTE))
-- 30分钟后的分钟数
MINUTE(DATE_SUB(NOW(), INTERVAL -30 MINUTE))
5.2 日期查询
查询游戏今日登录用户数
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND TO_DAYS(NOW()) = TO_DAYS(FROM_UNIXTIME(logintime))
查询游戏昨日登录用户数
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND TO_DAYS(NOW())-TO_DAYS(FROM_UNIXTIME(logintime)) <= 1
查询游戏最近7天用户登录数
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(logintime)) <= 7
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(FROM_UNIXTIME(logintime))
查询游戏本周登录用户数
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND YEARWEEK(NOW()) = YEARWEEK(FROM_UNIXTIME(logintime,'%Y-%m-%d'))
查询游戏上周用户登录数
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND YEARWEEK(NOW()) - YEARWEEK(FROM_UNIXTIME(logintime,'%Y-%m-%d')) = 1
查询游戏本月登录用户数
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND DATE_FORMAT(CURDATE(), '%Y%m') = FROM_UNIXTIME(logintime, '%Y%m')
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND MONTH(NOW()) - MONTH(FROM_UNIXTIME(logintime)) = 0
查询游戏上月登录用户数
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'), FROM_UNIXTIME(logintime, '%Y%m')) = 1
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND MONTH(NOW()) - MONTH(FROM_UNIXTIME(logintime)) = 1
查询游戏本季度用户登录数
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND QUARTER(NOW()) = QUARTER(FROM_UNIXTIME(logintime))
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND QUARTER(NOW()) - QUARTER(FROM_UNIXTIME(logintime)) = 0
查询游戏上季度用户登录数
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND QUARTER(DATE_SUB(NOW(), INTERVAL 1 QUARTER)) = QUARTER(FROM_UNIXTIME(logintime))
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND QUARTER(NOW()) - QUARTER(FROM_UNIXTIME(logintime)) = 1
查询游戏今年用户登录数
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND YEAR(NOW()) - YEAR(FROM_UNIXTIME(logintime)) = 0
查询游戏去年用户登录数
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND YEAR(NOW()) - YEAR(FROM_UNIXTIME(logintime)) = 1
SELECT
COUNT(1) AS cnt
FROM game_user
WHERE 1=1
AND YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR)) = YEAR(FROM_UNIXTIME(logintime))
三、Postgresql
1、获取系统时间函数
1.1 获取当前完整时间
select now();
david=# select now();
now
-------------------------------
2013-04-12 15:39:40.399711+08
(1 row)
david=#
current_timestamp 同 now() 函数等效。
david=# select current_timestamp;
now
-------------------------------
2013-04-12 15:40:22.398709+08
(1 row)
david=#
1.2 获取当前日期
select current_date;
david=# select current_date;
date
------------
2013-04-12
(1 row)
david=#
1.3 获取当前时间
select current_time;
david=# select current_time;
timetz
--------------------
15:43:31.101726+08
(1 row)
david=#
2、时间的计算
david=# select now();
now
-------------------------------
2013-04-12 15:47:13.244721+08
(1 row)
david=#
2.1 两年后
david=# select now() + interval '2 years';
?column?
-------------------------------
2015-04-12 15:49:03.168851+08
(1 row)
david=# select now() + interval '2 year';
?column?
-------------------------------
2015-04-12 15:49:12.378727+08
(1 row)
david=# select now() + interval '2 y';
?column?
------------------------------
2015-04-12 15:49:25.46986+08
(1 row)
david=# select now() + interval '2 Y';
?column?
-------------------------------
2015-04-12 15:49:28.410853+08
(1 row)
david=# select now() + interval '2Y';
?column?
-------------------------------
2015-04-12 15:49:31.122831+08
(1 row)
david=#
2.2 一个月后
david=# select now() + interval '1 month';
?column?
------------------------------
2013-05-12 15:51:22.24373+08
(1 row)
david=# select now() + interval 'one month';
ERROR: invalid input syntax for type interval: "one month"
LINE 1: select now() + interval 'one month';
^
david=#
2.3 三周前
david=# select now() - interval '3 week';
?column?
-------------------------------
2013-03-22 16:00:04.203735+08
(1 row)
david=#
2.4 十分钟后
david=# select now() + '10 min';
?column?
-------------------------------
2013-04-12 16:12:47.445744+08
(1 row)
david=#
说明:
interval 可以不写,其值可以是:
Abbreviation | Meaning |
Y | Years |
M | Months (in the date part) |
W | Weeks |
D | Days |
H | Hours |
M | Minutes (in the time part) |
S | Seconds |
2.5 计算两个时间差
使用 age(timestamp, timestamp)
david=# select age(now(), timestamp '1989-02-05');
age
----------------------------------------
24 years 2 mons 7 days 17:05:49.119848
(1 row)
david=#
david=# select age(timestamp '2007-09-15');
age
------------------------
5 years 6 mons 27 days
(1 row)
david=#
3、时间字段的截取
在开发过程中,经常要取日期的年,月,日,小时等值,PostgreSQL 提供一个非常便利的EXTRACT函数。
EXTRACT(field FROM source)
field 表示取的时间对象,source 表示取的日期来源,类型为 timestamp、time 或 interval。
3.1 取年份
david=# select extract(year from now());
date_part
-----------
2013
(1 row)
david=#
3.2 取月份
david=# select extract(month from now());
date_part
-----------
4
(1 row)
david=#
david=# select extract(day from timestamp '2013-04-13');
date_part
-----------
13
(1 row)
david=#
david=# SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
date_part
-----------
40
(1 row)
david=#
3.3 查看今天是一年中的第几天
david=# select extract(doy from now());
date_part
-----------
102
(1 row)
david=#
3.4 查看现在距1970-01-01 00:00:00 UTC 的秒数
david=# select extract(epoch from now());
date_part
------------------
1365755907.94474
(1 row)
david=#
3.5 把epoch 值转换回时间戳
david=# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1369755555 * INTERVAL '1 second';
?column?
------------------------
2013-05-28 23:39:15+08
(1 row)
david=#
四、SqlServer
DECLARE @Date DATETIME
SET @Date=GETDATE()
--前一天,给定日期的前一天
SELECT DATEADD(DAY,-1,@Date) AS '前一天'
--后一天,给定日期的后一天
SELECT DATEADD(DAY,1,@Date) AS '后一天'
GO
--月初,计算给定日期所在月的第一天
--这个计算的技巧是先计算当前日期到“1900-01-01”的时间间隔数,然后把它加到“1900-01-01”上来获得特殊的日期,这个技巧可以用---来计算很多不同的日期。
DECLARE @Date DATETIME
SET @Date=GETDATE()
SELECT DATEADD(MONTH,DATEDIFF(MONTH,'1900-01-01',@Date),'1900-01-01') AS '所在月的第一天'
--精简算法,根据SQL Server的时间表示方式可知,'1900-01-01' 可以用0代替
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '所在月的第一天'
--上面两种算法精确到天 时分秒均为00:00:00.000
--下面算法课以保留时分秒
--思路:用给定日期减去月第一天与给定日期差的天数
SELECT DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)
GO
--月末,计算给定日期所在月的最后一天
DECLARE @Date DATETIME
SET @Date=GETDATE()
--思路:当前月的下一月1号在减1天
SELECT DATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,'1900-01-01',@Date),'1900-01-01')) AS '所在月的最一天'
SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,'1900-01-01',@Date),'1900-01-01')-1 AS '所在月的最一天'
--1900-01-01 用0代替
SELECT DATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '所在月的最一天'
SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)-1 AS '所在月的最一天'
--思路:与月初计算思路相同
SELECT DATEADD(MONTH,DATEDIFF(MONTH,'1989-12-31',@Date),'1989-12-31') AS '所在月的最一天'
--精简算法,'1989-12-31' 用-1代替
SELECT DATEADD(MONTH,DATEDIFF(MONTH,-1,@Date),-1) AS '所在月的最一天'
--保留时分秒的算法
SELECT DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)))
GO
--其他月计算
--计算给定日期所在月的上月第一天
DECLARE @Date DATETIME
SET @Date=GETDATE()
--当前月第一天减去一个月
SELECT DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0)) AS '上月第一天'
--简化
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天'
--另一种当前月第一天算法
SELECT DATEADD(MONTH,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) '上月第一天'
GO
--计算给定日期所在月的上月最后一天
DECLARE @Date DATETIME
SET @Date=GETDATE()
--当前月第一天减去一天
SELECT DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0)) AS '上月最后一天'
--另一种当前月第一天算法
SELECT DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) '上月最后一天'
SELECT DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)-1 '上月最后一天'
--另一种算法,不能用当前月的最后一天加一个月,因为当前月可能是30天。
--例如 SELECT DATEADD(MONTH,1,'2010-06-30') --结果是2010-07-30而不是2010-07-31,
--这也是月末算法采用下月第一天减1天计算的原因
--但是如果计算月是31天择无此问题
--例如 SELECT DATEADD(MONTH,1,'2010-05-31') --结果是2010-06-30
--因此下面算法是正确的,-1 表示'1899-12-31 00:00:00.000'-- SELECT CONVERT(DATETIME,-1)
SELECT DATEADD(MONTH,DATEDIFF(MONTH,-1,@Date)-1,-1)
--另一种当前月算法
SELECT DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) '上月最后一天'
--简化
SELECT DATEADD(DAY,0-DATEPART(DAY,@Date),@Date) '上月最后一天'
GO
--计算给定日期所在月的下月第一天
DECLARE @Date DATETIME
SET @Date=GETDATE()
--当前月第一天加一个月
SELECT DATEADD(MONTH,1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0)) AS '下月第一天'
--简化
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天'
--另一种当前月第一天算法
SELECT DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) '下月第一天'
GO
--计算给定日期所在月的下月最后一天
DECLARE @Date DATETIME
SET @Date=GETDATE()
--当前月第一天加2个月再减去1天
SELECT DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0))) AS '下月最后一天'
--简化
SELECT DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+2,0)) AS '下月最后一天'
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+2,0)-1 AS '下月最后一天'
--另一种算法
SELECT DATEADD(MONTH,DATEDIFF(MONTH,-1,@Date)+1,-1) '下月最后一天'
--另一种当前月第一天算法
SELECT DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) '下月最后一天'
GO
--所在星期的第一天,计算给定日期所在星期的第1天(星期日为第一天)
DECLARE @Date DATETIME
SET @Date= GETDATE()
--与SQL Server语言版本相关的算法
--思路:当前日期+星期日(每周的第1天)与当前日期的差的天数
--DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关
SET DATEFIRST 7 -- 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天)
SELECT DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第一天,星期日'
--星期日,与SQL Server语言版本或@@DATEFIRST无关
--'1989-12-31' 是星期日,'1989-12-31' 再加上(当前日期与1989-12-31差的星期数)个星期
SELECT DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'
--或者
SELECT DATEADD(WEEK,DATEDIFF(WEEK,6,@Date),6) AS '所在星期的星期日'
GO
--所在星期的第二天,计算给定日期所在星期的第2天(星期日为第一天)
DECLARE @Date DATETIME
SET @Date= GETDATE()
--思路:当前日期+星期一(每周的第2天)与当前日期的差的天数
--DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关
SET DATEFIRST 7 -- 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天)
SELECT DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天,星期一'
--星期一,与SQL Server语言版本或@@DATEFIRST无关
--'1900-01-01' 是星期一,'1900-01-01' 再加上(当前日期与1900-01-01差的星期数)个星期
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,@Date),0) AS '所在星期的星期一'
GO
--上个星期第一天,计算给定日期所在星期的上一个星期日(星期日为第一天)
DECLARE @Date DATETIME
SET @Date= GETDATE()
--思路:当前日志所在星期的星期日再减1周
--DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关
SET DATEFIRST 7 -- 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天)
SELECT DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期第一天,星期日'
--一周等于7天
SELECT DATEADD(DAY,-7,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期第一天,星期日'
--简化
SELECT DATEADD(DAY,-6-DATEPART(WEEKDAY,@Date),@Date) AS '上个星期第一天,星期日'
--上个星期日,与SQL Server语言版本或@@DATEFIRST无关
SELECT DATEADD(WEEK,-1+DATEDIFF(WEEK,-1,@Date),-1) AS '上个星期日'
--或者
SELECT DATEADD(WEEK,DATEDIFF(WEEK,6,@Date),-1) AS '上个星期日'
GO
--下个星期第一天,计算给定日期所在星期的下一个星期日(星期日为第一天)
DECLARE @Date DATETIME
SET @Date= GETDATE()
--思路:当前日志所在星期的星期日再加1周
--DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关
SET DATEFIRST 7 -- 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天)
SELECT DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期第一天,星期日'
--一周等于7天
SELECT DATEADD(DAY,7,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期第一天,星期日'
--简化
SELECT DATEADD(DAY,8-DATEPART(WEEKDAY,@Date),@Date) AS '下个星期第一天,星期日'
--下个星期日,与SQL Server语言版本或@@DATEFIRST无关
SELECT DATEADD(WEEK,1+DATEDIFF(WEEK,-1,@Date),-1) AS '下个星期日'
--或者
SELECT DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),6) AS '下个星期日'
GO
--判断给定日期是星期几
DECLARE @Date DATETIME
SET @Date= GETDATE()
--DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关
SET DATEFIRST 7 -- 或者设置为美国英语SET LANGUAGE us_english; (星期日为第一天)
SELECT DATEPART(WEEKDAY,@Date) --返回值 1-星期日,2-星期一,3-星期二......7-星期六
--上面算法与SQL 语言版本或 @@DATEFIRST 相关
--下面算法与SQL Server语言版本或@@DATEFIRST无关
SELECT DATENAME(WEEKDAY,@Date) '星期'
GO
--年度计算
DECLARE @Date DATETIME
SET @Date=GETDATE()
--年初,计算给定日期所在年的第一天
SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '所在年的第一天'
--年末,计算给定日期所在年的最后一天
SELECT DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '所在年的最后一天'
--上一年年初,计算给定日期所在年的上一年的第一天
SELECT DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '所在年的上一年的第一天'
--上一年年末,计算给定日期所在年的上一年的最后一天
SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),-1) AS '所在年的上一年的最后一天'
--下一年年初,计算给定日期所在年的下一年的第一天
SELECT DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '所在年的下一年的第一天'
--下一年年末,计算给定日期所在年的下一年的最后一天
SELECT DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '所在年的下一年的最后一天'
GO
--季度计算
DECLARE @Date DATETIME
SET @Date=GETDATE()
--季度初,计算给定日期所在季度的第一天
SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '当前季度的第一天'
--季度末,计算给定日期所在季度的最后一天
SELECT DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '当前季度的最后一天'
--上个季度初
SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '当前季度的上个季度初'
--上个季度末
SELECT DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '当前季度的上个季度末'
--下个季度初
SELECT DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '当前季度的下个季度初'
--下个季度末
SELECT DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '当前季度的下个季度末'
--计算给定日期所在月的天数
DECLARE @Date DATETIME;
SET @Date = GETDATE()
--本月度第一天与下月度第一天所差的天数
SELECT DATEDIFF(DAY,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0),DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0))
--借助变量简化
SELECT @Date = DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) --本月度第一天
SELECT DATEDIFF(DAY,@Date,DATEADD(MONTH,1,@Date))
--另一种思路:给定月最后一天的日期,记为本月天数
SELECT DAY(DATEADD(MONTH,DATEDIFF(MONTH,-1,@Date),-1))
GO
--计算给定日期所在季度的天数
DECLARE @Date DATETIME;
SET @Date = GETDATE()
--本季度第一天与下季度第一天所差的天数
SELECT DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0),DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0))
--借助变量简化
SELECT @Date = DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) --本季度第一天
SELECT DATEDIFF(DAY,@Date,DATEADD(QUARTER,1,@Date))
GO
--计算给定日期所在年度的天数
DECLARE @Date DATETIME;
SET @Date = GETDATE()
--本年度第一天与下年度第一天所差的天数
SELECT DATEDIFF(DAY,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0),DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0))
--借助变量简化
SELECT @Date = DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) --本年度第一天
SELECT DATEDIFF(DAY,@Date,DATEADD(YEAR,1,@Date))
GO
--判断给定日期所在年是否闰年
--根据全年总天数判断
DECLARE @Date DATETIME;
SET @Date = GETDATE()
SELECT CASE DATEDIFF(DAY,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0),DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0))
WHEN 365 THEN '平年' ELSE '闰年' END
--根据二月天数判断
--给日期的上一年最后一天加2个月,即为当年2月最后一天
SELECT CASE DAY(DATEADD(MONTH,2,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),-1))) WHEN 28 THEN '平年' ELSE '闰年' END
GO
--计算给定日期是当年的第几天
DECLARE @Date DATETIME;
SET @Date = GETDATE()
SELECT DATEPART(DAYOFYEAR,@Date) [DayOfYear];
SELECT DATENAME(DAYOFYEAR,@Date) [DayOfYear];
--另一种思路:当前日期与上年最后一天差的天数
SELECT DATEDIFF(DAY,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),-1),@Date)[DayOfYear]
GO
--计算给定日期是当年的第几周
DECLARE @Date DATETIME;
SET @Date = GETDATE()
SELECT DATEPART(WEEK,@Date) [WeekOfYear]; --返回int型
SELECT DATENAME(WEEK,@Date) [WeekOfYear]; --返回varchar型
GO
--计算给定日期是当年的第几月
DECLARE @Date DATETIME;
SET @Date = GETDATE()
SELECT DATEPART(MONTH,@Date) [MonthOfYear]; --返回int型
SELECT DATENAME(MONTH,@Date) [MonthOfYear]; --返回varchar型
SELECT MONTH(@Date) [MonthOfYear];--返回int型
GO
--计算给定日期是当年的第几季度
DECLARE @Date DATETIME;
SET @Date = GETDATE()
SELECT DATEPART(QUARTER,@Date) [QuarterOfYear]; --返回int型
SELECT DATENAME(QUARTER,@Date) [QuarterOfYear]; --返回varchar型
GO
--计算给定日期是当月的第几周
DECLARE @Date DATETIME;
SET @Date = GETDATE()
--思路,给定日期是当年的第几周-给定日期所在月第一天是当年的第几周
SELECT DATEPART(WEEK,@Date)-DATEPART(WEEK,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0))+1 [WeekOfMonth]
SELECT DATEPART(WEEK,@Date)-DATEPART(WEEK,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))+1 [WeekOfMonth]
GO
--计算给定日期所在月的第一个星期一是哪天
DECLARE @Date DATETIME;
SET @Date = GETDATE()
--思路,1900-01-01(星期一)加上(给定日志所在月的月6号与1900-01-01差的周数)个周
--为什么不选7号?如果是7号,那么7好恰好是星期日的话,第一个周一就会算到8号。
--为什么不选5号?如果5号是星期六,那么周一就跑到上月了。小于5号与这个道理一样。
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(DAY,6-DATEPART(DAY,@Date),@Date)),0) '所在月的第一个星期一'
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(DAY,6-DATEPART(DAY,@Date),@Date)),7) '所在月的第二个星期一'
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(DAY,6-DATEPART(DAY,@Date),@Date)),1) '所在月的第一个星期二'
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(DAY,6-DATEPART(DAY,@Date),@Date)),8) '所在月的第二个星期二'
#@Le.Hao#
帮助到您请点赞关注收藏谢谢!!