[SQLite] Date Diff를 구하는 방법

How to look up the Date Interval

만약 아래와 같은 테이블이 있다고 가정하면,

TITLE	DATE_ST	DATE_ED
CASE #1	2023-07-01 09:00:00.000	2023-07-02 10:10:10.000
CASE #2	2023-06-01 09:00:00.000	2023-07-02 10:10:10.000
CASE #3	2022-07-01 09:00:00.000	2023-07-02 10:10:10.000

CASE #1 : 시작 일자와 종료 일자가 1일 1시간 10분 10초 차이

CASE #2 : 시작 일자와 종료 일자가 1개월 1일 1시간 10분 10초 차이

CASE #3 : 시작 일자와 종료 일자가 1년 1일 1시간 10분 10초 차이

 

1. 테이블 생성

CREATE TABLE TESTDATE (
	TITLE 	TEXT
  , DATE_ST	TEXT
  , DATE_ED	TEXT
)

SQLite에는 별도의 Date Type이 없으므로, 문자열 형태의 TEXT 또는 TIMESTAMP로 저장하기 위해 REAL을 이용해야 합니다.

 

2. 테스트 데이터 추가

INSERT INTO TESTDATE (TITLE, DATE_ST, DATE_ED) VALUES ('CASE #1', '2023-07-01 09:00:00.000', '2023-07-02 10:10:10.000')

INSERT INTO TESTDATE (TITLE, DATE_ST, DATE_ED) VALUES ('CASE #2', '2023-06-01 09:00:00.000', '2023-07-02 10:10:10.000')

INSERT INTO TESTDATE (TITLE, DATE_ST, DATE_ED) VALUES ('CASE #1', '2022-07-01 09:00:00.000', '2023-07-02 10:10:10.000')

위 Query를 실행하면 아래와 같이 테이블에 데이터가 입력 되었음을 확인 가능합니다.

테스트 데이터 등록된 결과

 

3. 율리우스일 (JULIANDAY)

위키백과 "율리우스일"

SQLite에서는 JULIANDAY 함수를 제공합니다. 때문에 각 날짜는 아래와 같이 Query를 수행하여 율리우스일로 변환 가능합니다.

SELECT JULIANDAY('NOW')
     , JULIANDAY('2023-07-01 09:00:00.000')

율리우스일 결과

 

4. DATEDIFF

SQLite에서는 DATE형이 없으니 날짜 계산을 위한 DATEDIFF나 시간 계산을 위한 TIMEDIFF가 없으니, 율리우스일 기준으로 날짜 간격을 구한 후 별도의 계산을 해주어야 합니다. 때문에, 정확한 연/월 까지는 단순 Query로는 어렵고, 일/시/분/초는 가능합니다.

SELECT TITLE
	 , DATE_ST
	 , DATE_ED
	 , CAST((JULIANDAY(DATE_ED) - JULIANDAY(DATE_ST)) / 30 / 12			AS INTEGER)	AS DIFF_YEAR
	 , CAST((JULIANDAY(DATE_ED) - JULIANDAY(DATE_ST)) / 30				AS INTEGER)	AS DIFF_MONTH
	 , CAST((JULIANDAY(DATE_ED) - JULIANDAY(DATE_ST)) 					AS INTEGER)	AS DIFF_DATE
	 , CAST((JULIANDAY(DATE_ED) - JULIANDAY(DATE_ST)) * 24 				AS INTEGER)	AS DIFF_HOUR
	 , CAST((JULIANDAY(DATE_ED) - JULIANDAY(DATE_ST)) * 24 * 60 		AS INTEGER)	AS DIFF_MINUTE
	 , CAST((JULIANDAY(DATE_ED) - JULIANDAY(DATE_ST)) * 24 * 60 * 60	AS INTEGER)	AS DIFF_SECOND
  FROM TESTDATE

일자 차이 결과

 

5. STRFTIME

날짜/시간을 문자열(String)으로 변환해주는 함수입니다.

SELECT JULIANDAY('NOW') AS TODAY
     , JULIANDAY('2023-07-01 09:00:00.000') AS JULIAN
     , STRFTIME('%Y%m', '2023-07-01 09:00:00.000') AS YYYYMM
     , STRFTIME('%m', '2023-08-01 09:00:00.000') AS MM

만약 위의 3, 4번 줄과 같이 사용한다면, 각각 연월과 월을 반환하여

3번은 202307

4번은 08

을 반환하게 됩니다.

조회 결과

이를 활용하여, JULIANDAY에서 미흡했던 연/월에 대한 계산도 가능하게 됩니다.

SELECT TITLE
	 , DATE_ST
	 , DATE_ED
	 , CAST((STRFTIME('%Y', DATE_ED) - STRFTIME('%Y', DATE_ST))			AS INTEGER)	AS DIFF_YEAR
	 , 	 CAST((STRFTIME('%Y', DATE_ED) - STRFTIME('%Y', DATE_ST))	AS INTEGER) * 12
	   + CAST((STRFTIME('%m', DATE_ED) - STRFTIME('%m', DATE_ST))	AS INTEGER)		AS DIFF_MONTH
	 , CAST((JULIANDAY(DATE_ED) - JULIANDAY(DATE_ST)) 					AS INTEGER)	AS DIFF_DATE
	 , CAST((JULIANDAY(DATE_ED) - JULIANDAY(DATE_ST)) * 24 				AS INTEGER)	AS DIFF_HOUR
	 , CAST((JULIANDAY(DATE_ED) - JULIANDAY(DATE_ST)) * 24 * 60 		AS INTEGER)	AS DIFF_MINUTE
	 , CAST((JULIANDAY(DATE_ED) - JULIANDAY(DATE_ST)) * 24 * 60 * 60	AS INTEGER)	AS DIFF_SECOND
  FROM TESTDATE

STRTIME 함수에 대한 자세한 사항은 아래 사이트에서 확인 가능합니다.

https://www.ibm.com/docs/ko/i/7.3?topic=functions-strftime-convert-datetime-string

strftime() — 날짜/시간을 스트링으로 변환

 

※ 이 글은 워드프레스에 작성한 글과 동일한 작성자의 동일한 글입니다.

https://royfactory.net/2023/07/03/sqlite-to-lookup-date-diff/

[SQLite] How to look up the date interval (Date Diff) - ROY FACTORY