[ORA9.2] 날짜 및 시간 함수, 연산자

/********************************************************************************************
-- Title : [ORA9.2] 날짜 및 시간 함수, 연산자
-- Reference : http://www.postgresql.org/docs/9.2/static/functions-datetime.html
-- Key word : current_date current_timestamp now time date
********************************************************************************************/

-- 시스템 일자 

select current_date, current_time, timeofday();

    result: 2013-07-30; 13:07:48.18815+09; tue jul 30 13:07:48.188353 2013 kst

select now(), current_timestamp, timestamp 'now';

    result: 2013-07-30 13:20:30.982742+09; 2013-07-30 13:20:30.982742+09; 2013-07-30 13:20:30.982742

 

-- 년도 추출

select date_part('year', timestamp '2013-07-30 20:38:40');

    result: 2013

select date_part('year', current_timestamp);

    result: 2013

select extract('isoyear' from date '2006-01-01');

    result: 2005

select extract('isoyear' from current_timestamp);

    result: 2013

select extract('isoyear' from date '2006-01-02');

    result: 2006

select date_trunc('year', timestamp '2013-07-30 20:38:40');

    result: 2013-01-01 00:00:00

select date_trunc('year', current_timestamp);

    result: 2013-01-01 00:00:00+09

 

-- 월 추출

select date_part('month', timestamp '2013-07-30 20:38:40');

    result: 7

select date_part('month', current_timestamp);

    result: 7

select extract('month' from timestamp '2013-07-30 20:38:40');

    result: 7

select extract('month' from interval '2 years 3 months');

    result: 3

select extract('month' from interval '2 years 13 months');

    result: 1

select date_trunc('month', timestamp '2013-07-30 20:38:40');

    result: 2013-07-01 00:00:00

    

-- 일 추출

select date_part('day', timestamp '2013-07-30 20:38:40');

    result: 30

select date_trunc('day', timestamp '2013-07-30 20:38:40');

    result: 2013-07-30 00:00:00

 

-- 시간 추출

select date_part('hour', timestamp '2013-07-30 20:38:40');

    result: 20

select date_part('hour', interval '4 hours 3 minutes');

    result: 4

select date_trunc('hour', timestamp '2013-07-30 20:38:40');

    result: 2013-07-30 20:00:00

 

-- 분 추출

select date_part('minute', timestamp '2013-07-30 20:38:40');

    result: 38

select date_trunc('minute', timestamp '2013-07-30 20:38:40');

    result: 2013-07-30 20:38:00

 

-- 초 추출

select date_part('second', timestamp '2013-07-30 20:38:40');

    result: 40

select extract('second' from time '17:12:28.5');

    result: 28.5

select date_trunc('second', timestamp '2013-07-30 20:38:40');

    result: 2013-07-30 20:38:40

 

-- 세기(century) 추출

select extract('century' from timestamp '2000-12-16 12:21:13');

    result: 20

select extract('century' from timestamp '2013-07-30 20:38:40');

    result: 21

select date_trunc('century', timestamp '2013-07-30 20:38:40');

    result: 2001-01-01 00:00:00

 

 

-- 요일/일차 추출

select extract('dow' from timestamp '2013-07-30 20:38:40');    -- 일요일(0), 토요일(6)

    result: 5

select extract('isodow' from timestamp '2013-07-30 20:38:40'); -- 월요일(1), 일요일(7)

    result: 5  -- 

select extract('doy' from timestamp '2013-07-30 20:38:40');

    result: 47