RDBMS (PostgreSQL)
PostgreSQL 날짜&시간 사용하기
[하마] 이승현 (wowlsh93@gmail.com)
2015. 5. 13. 11:01
PostgreSQL Date & Time Function
해당 날짜의 데이터
select count(watt_max) from tbl_test_watt3_sm2ch_min
where to_char(regdate, 'YYYY-MM-DD') = '2016-10-17'
where to_char(regdate, 'YYYY-MM-DD') = '2016-10-17'
소요시간 : 124초 (150만건) (하루: 20*60*60*24 = 1,728,000) 이렇게 하면 망함!
해당 날짜의 데이터
select count(watt) from tbl_test_watt_lsh where
regdate >= date '2016-10-17'
and regdate < date '2016-10-17' + integer '1' // 여기선 하루
소요시간 : 634ms (150만건) (하루: 20*60*60*24 = 1,728,000)
해당 날짜의 데이터
select count(watt) from tbl_test_watt_lsh where
regdate >= current_date
and regdate < current_date + 1
소요시간 : 634ms (150만건) (하루: 20*60*60*24 = 1,728,000)
해당 시간의 데이터
select count(watt) from tbl_test_watt_lsh where regdate
between to_timestamp('2016-10-17 07:40:00' , 'YYYY-MM-DD HH24:MI:SS') and to_timestamp('2016-10-17 07:43:00', 'YYYY-MM-DD HH24:MI:SS')
소요시간 : 14ms (3600건) (하루: 20*60*60*24 = 1,728,000)
select count(watt) from tbl_test_watt_lsh where regdate
between to_timestamp('2016-10-17 07:40:00' , 'YYYY-MM-DD HH24:MI:SS') and to_timestamp('2016-10-17 07:43:00', 'YYYY-MM-DD HH24:MI:SS') + interval '1' // 여기선 1초
소요시간 : 14ms (3620건)
select count(watt) from tbl_test_watt_lsh where regdate
between to_timestamp('2016-10-17 07:40:00' , 'YYYY-MM-DD HH24:MI:SS') and to_timestamp('2016-10-17 07:43:00', 'YYYY-MM-DD HH24:MI:SS') + interval '1' HOUR // 여기선 1 시간
소요시간 : 23ms (63340건)
select count(watt) from tbl_test_watt_lsh where regdate
between to_timestamp('2016-10-17 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') and to_timestamp('2016-10-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + '1-1 00:00:00' // 여기선 하루 , 인터벌 값을 이렇게 나타낼 수 있다
'10-10' 는 10년 10개월
소요시간 : 433ms (1496720건)
select * from tbl_test_watt_lsh where to_char(regdate , 'YYYY-MM-DD HH24;MI:SS') > '2016-10-17 07:40:00' AND to_char(regdate , 'YYYY-MM-DD HH24;MI:SS') < '2016-10-17 07:43:00'
역시 to_char 사용하면 망함!
http://daemonjin.tistory.com/564 펌
간단한 날짜 관련 함수 및 날짜 연산:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | -- 오늘 (date) select current_date ;
-- 현재시각 (timestamp) select now(); select current_timestamp ;
-- 어제/오늘/내일 select current_date - 1 "어제" , current_date "오늘" , current_date + 1 "내일" ;
-- day of week select extract(dow from current_date ); -- 일요일(0) ~ 토요일(6) select extract(isodow from current_date ); -- 월요일(1) ~ 일요일(7)
-- day of year select extract(doy from current_date );
-- week of year select extract(week from current_date );
-- 두 날짜 사이의 날수 select '2010-07-05' :: date - '2010-06-25' :: date ;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
-- 주 첫 날을 월요일로 할 때 주 첫날, 마지막 날 -- date_trunc() 함수의 리턴 타입은 timestamp임 -- 이번 주 select date_trunc( 'week' , current_date ):: date "이번 주 첫날" , date_trunc( 'week' , current_date ):: date + 6 "이번 주 마지막 날" ; -- 전 주 select date_trunc( 'week' , current_date - 7):: date "전 주 첫날" , date_trunc( 'week' , current_date - 7):: date + 6 "전주 마지막 날" ; -- 다음 주 select date_trunc( 'week' , current_date + 7):: date "다음 주 첫날" , date_trunc( 'week' , current_date + 7):: date + 6 "다음주 마지막 날" ; -- (주 첫 날을 일요일로 할 때) 주 첫날/마지막 날 -- week로 date_trunc를 하는 경우 결과가 월요일 날짜가 되기 때문에 -- 한 주를 일요일~토요일까지로 하는 경우는 -1 필요 -- 이번 주 select date_trunc( 'week' , current_date ):: date - 1 "이번 주 첫날" , date_trunc( 'week' , current_date ):: date + 6 - 1 "이번 주 마지막 날" ; -- 전 주 select date_trunc( 'week' , current_date - 7):: date - 1 "전 주 첫날" , date_trunc( 'week' , current_date - 7):: date + 6 - 1 "전주 마지막 날" ; -- 다음 주 select date_trunc( 'week' , current_date + 7):: date - 1 "다음 주 첫날" , date_trunc( 'week' , current_date + 7):: date + 6 - 1 "다음주 마지막 날" ;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- 한 달 전/후 날짜 select current_date - interval '1 months' "전 달" , current_date + interval '1 months' "다음 달" ; -- 이번 달 첫날, 마지막 날 select date_trunc( 'month' , current_date ):: date "첫날" , date_trunc( 'month' , current_date + interval '1 months' ):: date - 1 "마지막 날" ; -- 전달 첫날, 마지막 날 select date_trunc( 'month' , current_date - interval '1 months' ):: date "첫 날" , date_trunc( 'month' , current_date ):: date - 1 "마지막 날" ; -- 다음 달 첫날, 마지막 날 select date_trunc( 'month' , current_date + interval '1 months' ):: date "첫 날" , date_trunc( 'month' , current_date + interval '2 months' ):: date - 1 "마지막 날" ;
|
1 2 3 4 5 |
-- 이번 주 날짜 select date_trunc( 'week' , current_date ):: date -1 + i "일~토" , date_trunc( 'week' , current_date ):: date + i "월~일" from generate_series(0,6) as t(i);
|
한 달이 28일, 29일, 30일, 31일 중 어떤 것이 될지 알 수 없기 때문에 월의 마지막날을 구해
generate_series()의 두번째 인수로 넣어준다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | -- 이번 달 날짜 (첫날 ~ 마지막 날) select date_trunc( 'month' , current_date ):: date + (i - 1) from generate_series( 1, extract( day from date_trunc( 'month' , current_date + interval '1 months' ):: date - 1):: integer ) as t(i); select date_trunc( 'month' , current_date ):: date + (i - 1) from generate_series( 1, extract( day from date_trunc( 'month' , current_date ) + interval '1 months' - interval '1 days' ):: integer ) as t(i);
|
이번 달의 첫날부터 마지막 날까지의 날짜와 week of month를 구하는 쿼리인데, 1일~7일까지는 첫째 주, 8
일~14일 까지는 둘째 주와 같은 식으로 된다. 역시 generate_series() 함수를 사용했는데, 위와 같이 첫 날과
마지막 날의 차를 구해 수열을 만들지 않고 0~30까지 만들어 무조건 더하면서 이번 달에 속하는 날짜만
where 절 조건으로 추려내게 했다.
1 2 3 4 5 6 7 | select dt, to_char(dt, 'W' ) "day of week" from ( select date_trunc( 'month' , current_date ):: date + i dt from generate_series(0, 30) as t(i) ) t where extract( month from dt) = extract( month from current_date ) ;
|