[postgreSQL] 일시 저장 및 조회

insert into test(testtime) values(systimestamp);

insert into test(testtime) values(to_timestamp('2009-01-03 12:33:33');

 

출처: https://hamait.tistory.com/208 [HAMA 블로그]

 

-- 해당 날짜의 데이터 

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건)

-- 오늘 (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;

 

-- 주 첫 날을 월요일로 할 때 주 첫날, 마지막 날

-- 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 "다음주 마지막 날"

;

 

-- 한 달 전/후 날짜

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 "마지막 날"

;

-- 이번 주 날짜

select

  date_trunc('week', current_date)::date -1 + i "일~토",

  date_trunc('week', current_date)::date    + i "월~일"

from generate_series(0,6) as t(i);

week of month: 

 

이번 달의 첫날부터 마지막 날까지의 날짜와 week of month를 구하는 쿼리인데, 1일~7일까지는 첫째 주, 8

일~14일 까지는 둘째 주와 같은 식으로 된다. 역시 generate_series() 함수를 사용했는데, 위와 같이 첫 날과

마지막 날의 차를 구해 수열을 만들지 않고 0~30까지 만들어 무조건 더하면서 이번 달에 속하는 날짜만 

where 절 조건으로 추려내게 했다.

 

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)

;