시간 데이터 유형

테이블과 데이터베이스를 설계하고 시간 데이터 유형을 선택할 때는 신중하게 고려해야 한다.

• 상황에 따라 전체 TIMESTAMPTZ 레벨이 필요하거나 필요하지 않을 수 있다.
• 타임존 확인
  • show timezone;
• 타임존 설정(변경)
  • set timezone = 'America/Los_Angeles'; 

 

Date and Time 정보와 관련된 데이터 타입

• TIME: 시간만 포함
  • 15:03:30.484561
• DATE: 날짜만 포함
  • 2023-05-16
• TIMESTAMP: 타임존을 명시하지 않은 날짜와 시간
  • 2023-05-16 15:03:30.484561
  • TIMESTAMP는 타임존에 따른 시간 변경이 적용되지 않는다.
• TIMESTAMPTZ: 타임존을 명시한 날짜와 시간
  • 2023-05-16 18:01:40.920477+09
  • 타임존이 변경되면 타임존에 따라 자동으로 계산된다.
  • 저장할 때는 UTC+0 기준 TIMESTAMP로 변환되어 저장되고, 출력할 일이 있을 때 pg의 TIMEZONE 혹은 설정한 존으로 변환해 출력하게 된다.
  • 만약 위에 시차가 17시간 차이나는 로스엔젤레스로 타임존을 변경하게 되면 2023-05-16 02:01:40.920477-07(서머타임 적용, 원래는 -8)로 변경된다.

 

날짜 및 시간 데이터 유형과 관련된 함수

• NOW
  • 현재시간을 타임존을 포함하여 반환한다.
  • 2023-05-16 18:25:24.08764+09
• TIMEOFDAY
  • 해당 함수는 현재 일자(타임존 포함)를 TEXT형으로 반환한다.
  • Tue May 16 09:53:56.298769 2023 KST
• CURRENT_TIME
  • 현재시간을 반환한다.
  • 09:54:47.214596+09
• CURRENT_DATE
  • 현재 날짜를 반환한다.
  • 2023-05-16

 

시간 데이터 유형을 사용하여 정보 추출

 

EXTRACT

• EXTRACT() 함수는 날짜/시간 데이터에서 year(년도), month(월), day(일)과 같은 요소를 추출/검색하는 함수
• EXTRACT(field FROM source)
  • field는 year, month, day 등의 날짜/시간 데이터 요소
  • source는 실제 timestamp 값을 의미한다. ex) '2023-05-16 15:00:00'

 

field 종류

• CENTURY
• DAY
• DOW
• DOY
• EPOCH
• HOUR
• MILLISECOND
• MINUTE
• MONTH
• QUARTER
• SECOND
• WEEK
• YEAR

 

예시

select 
	EXTRACT('CENTURY' FROM NOW()) AS century,
	EXTRACT('DAY' FROM NOW()) AS day,
	EXTRACT('DOW' FROM NOW()) AS dow,
	EXTRACT('DOY' FROM NOW()) AS doy,
	EXTRACT('EPOCH' FROM NOW()) AS epoch,
	EXTRACT('HOUR' FROM NOW()) AS hour,
	EXTRACT('MILLISECOND' FROM NOW()) AS millisecond,
	EXTRACT('MINUTE' FROM NOW()) AS minute,
	EXTRACT('MONTH' FROM NOW()) AS month,
	EXTRACT('QUARTER' FROM NOW()) AS quarter,
	EXTRACT('SECOND' FROM NOW()) AS second,
	EXTRACT('WEEK' FROM NOW()) AS week,
	EXTRACT('YEAR' FROM NOW()) AS year;

 

DATE_TRUNC

• DATE_TRUNC() 함수는 인수를 사용하여 다양한 날짜, 시간 형식으로 데이터를 자를때 사용한다.
• DATE_TRUNC() 함수에서 사용할 수 있는 인수 목록
  • millennium
  • century
  • decade
  • year
  • quarter
  • month
  • week
  • day
  • hour
  • minute
  • second
  • milliseconds
  • microseconds

 

예시

SELECT 
  DATE_TRUNC('year', NOW()) as year, -- 년 밑으로 자르기
  DATE_TRUNC('month', NOW()) as month, -- 월 밑으로 자르기
  DATE_TRUNC('day', NOW()) as day, -- 일 밑으로 자르기
  DATE_TRUNC('hour', NOW()) as hour, -- 시 밑으로 자르기
  DATE_TRUNC('minute', NOW()) as minute, -- 분 밑으로 자르기
  DATE_TRUNC('second', NOW()) as second; -- 초 밑으로 자르기

SELECT TO_CHAR(DATE_TRUNC('day', NOW()), 'YYYY.MM.DD.HH24.MI.SS'); -- 2023.05.15.00.00.00