설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


Powered by EnterpriseDB
총 게시물 84건, 최근 0 건
 

epoch 와 timezone

글쓴이 : PostgresDBA 날짜 : 2017-11-30 (목) 10:48 조회 : 42
SELECT 
EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2018-11-01 00:00:00'),
EXTRACT(EPOCH FROM TIMESTAMP WITHOUT TIME ZONE '2018-11-01 00:00:00'),
EXTRACT(EPOCH FROM '2018-11-01 00:00:00'::TIMESTAMP);
----------------------------------------------------------------------
1540998000 1541030400 1541030400

epoch
For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for date and timestamp values, the number of seconds since 1970-01-01 00:00:00 local time; for interval values, the total number of seconds in the interval

-------------------------------------------------------------
특히 epas 에서 nvl 함수를 timestamp 와 사용할때는 주의해야 합니다.
nvl 함수는 인수가 timezone 이 없는 timestamp 를 입력해도 결과는 항상 timezone 이 붙어있는 값을 반환합니다.

with dat as (
select 
nvl(null,'2018-11-01 00:00:00'::timestamp) as a, 
nvl(null,'2018-11-01 00:00:00'::timestamp with time zone) as b, 

coalesce(null,'2018-11-01 00:00:00'::timestamp with time zone) as c, 
'2018-11-01 00:00:00'::timestamp with time zone as d,
coalesce(null,'2018-11-01 00:00:00'::timestamp) as e, 
'2018-11-01 00:00:00'::timestamp as f
)
select 
extract(epoch from a), extract(epoch from b), extract(epoch from c),
extract(epoch from d), extract(epoch from e), extract(epoch from f)
from dat;
------------------------------------------------------------------------------------------------
1540998000 1540998000 1540998000 1540998000 1541030400 1541030400

 

postgresdba.com