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
