SQLite | SQLite 함수 | 날짜 및 시간 구하기 (date 함수, time 함수, datetime 함수, julianday 함수, strftime 함수)
date 함수, time 함수, datetime 함수, julianday 함수, strftime 함수를 사용하면 현재 또는 지정된 날짜의 날짜와 시간을 구할 수 있다. 여기에서 date 함수, time 함수, datetime 함수, julianday 함수, strftime 함수의 사용법에 대해 설명한다.
날짜와 시간을 조회하는 방법
각각의 함수는 날짜와 시간을 조회하기 위해 사용한다. 형식은 다음과 같다.
date(timestring, modifier, modifier, ...)
time(timestring, modifier, modifier, ...)
datetime(timestring, modifier, modifier, ...)
julianday(timestring, modifier, modifier, ...)
strftime(format, timestring, modifier, modifier, ...)
인수로 timestring에 날짜와 시간을 나타내는 문자열을 지정하며, 각 함수마다 지정된 형식으로 변환하여 값을 반환한다.
각 함수의 포멧은 다음과 같다.
| 함수 | 포멧 |
|---|---|
| date 함수 | ‘YYYY-MM-DD’ |
| time 함수 | ‘HH:MM:SS’ |
| datetime 함수 | ‘YYYY-MM-DD HH:MM:SS’ |
| julianday 함수 | ‘J’ |
date 함수에 날짜와 시간을 나타내는 문자열을 인수로 전달하면, 날짜 부분인 YYYY-MM-DD 형식으로 반환한다. time 함수이면 시간의 부분을 HH:MM:SS 형식으로 반환한다. datetime 함수는 날짜 및 시간 값을 모두 반환한다. julianday 함수는 율리우스 날짜를 반환한다.
주의 사항
지정된 날짜와 시간의 시간대는 UTC로 처리된다.
strftime 함수는 형식을 직접 지정한다. 다른 함수와 strftime 함수를 같이 사용있다. 나중에 자세히 살펴 보도록 하겠다.
–
인수에 지정된 날짜와 시간을 나타내는 문자열은 다음 중 하나의 형식으로 지정한다.
YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD
예를 들면, ‘2019-08-22’와 ‘14:10:25 ‘, 그리고 ‘2019-03-01 09:01:45’ 등 이다. 시간에 대한 값이 생략된 경우는 ‘00:00:00.000 ‘이다. 또한 날짜가 생략된 경우는 ‘2000-01-01’이다.
특별한 값인 ’now’를 지정하면 현재 날짜와 시간을 설정한다. 시간대는 UTC이다.
‘DDDDDDDDDD’은 Julian day(율리우스 날짜) 형식이다. 이것은 양력 기원전 4714년 11월 24일 정오부터 일차로 지정한다.
date 함수, time 함수, datetime 함수를 사용하여 날짜 및 시간 조회
그러면 실제로 날짜 및 시간을 조회해 보자. 다음과 같이 SELECT 문을 실행한다. AS 절을 사용하여 컬럼 별명을 붙이고 있다. (AS 절 대한 자세한 내용은 “조회한 데이터의 컬럼에 별명 설정 (AS 절)“를 참조한다.)
select date('2008-04-28 09:15:42') as date,
time('2018-10-28 17:42:19') as time,
datetime('2019-11-06 12:03') as datetime;
sqlite> select date('2008-04-28 09:15:42') as date,
...> time('2018-10-28 17:42:19') as time,
...> datetime('2019-11-06 12:03') as datetime;
date time datetime
---------- ---------- -------------------
2008-04-28 17:42:19 2019-11-06 12:03:00
sqlite>
지정된 날짜 및 시간에서 날짜와 시간을 구했다.
다음은 now를 지정하여 현재 날짜와 시간을 구한다.
select date('now') as date,
time('now') as time,
datetime('now') as datetime;
sqlite> select date('now') as date,
...> time('now') as time,
...> datetime('now') as datetime;
date time datetime
---------- ---------- -------------------
2019-11-06 15:03:33 2019-11-06 15:03:33
sqlite>
현재 날짜와 시간(시간대는 UTC이다) 날짜와 시간을 구할 수 있었다.
julianday 함수를 사용하여 날짜 및 시간 가져
julianday 함수는 인수에 지정된 날짜와 시간을 율리우스 날짜로 가져온다. 율리우스 날짜는 양력 기원전 4714년 11월 24일 (유리아스 력의 기원전 4713 년 1월 1일) 정오을 기준일로 하여 기준일부터 일차를 나타낸 것이다. 단위는 일이다. 또한 기준일은 정오가 되고있는 점에 주의한다.
| 날짜 | 율리우스 날짜 |
|---|---|
| 기원전 4713년 11월 24일 12:00:00.000 | 0 |
| 기원전 4713년 11월 25일 00:00:00.000 | 0.5 |
| 2000년 1월 1일 00:00:00.000 | 2451544.5 |
| 2000년 1월 1일 12:00:00.000 | 2451545 |
율리우스 날짜는 단위가 일짜이 되고 있기에, 소수점 이하의 값은 다음과 같이 변환할 수 있다.
0.1 = 2.4 시간 / 144 분 / 8640 초
0.01 = 0.24 시간 / 14.4 분 / 864 초
0.001 = 0.024 시간 / 1.44 분 / 86.4 초
0.0001 = 0.0024 시간 / 0.144 분 / 8.64 초
0.00001 = 0.00024 시간 / 0.0144 분 / 0.864 초
그러면 실제로 구해보자. 다음과 같이 SELECT 문을 실행한다. AS 절을 사용하여 컬럼 별명을 붙이고 있다. (AS 절 대한 자세한 내용은 “조회한 데이터의 컬럼에 별명 설정 (AS 절)“를 참조한다.)
select julianday('2019-08-04 08:42:19') as julianday;
sqlite> select julianday('2019-08-04 08:42:19') as julianday;
julianday
----------------
2458699.86271991
sqlite>
지정된 날짜와 시간에서 율리우스 날짜를 구할 수 있었다.
strftime 함수를 사용하여 날짜 및 시간 조회
다른 함수에는 반환할 결과 형식이 정해져 있었지만 strftime 함수는 포맷을 지정할 수 있다.
포멧 지정은 임의의 문자와 아래와 같은 특수 문자를 조합하여 지정한다.
| 특수 문자 | 설명 |
|---|---|
| %Y | 년 : 0000-9999 |
| %m | 월 : 01-12 |
| %d | 일 |
| %W | 연초부터의 주수 : 00-53 |
| %j | 연초부터의 경과 일수 : 001-366 |
| %w | 요일 : 0-6 (일요일은 0, 토요일이 6) |
| %H | 시 : 00-24 |
| %M | 분 : 00-59 |
| %S | 초 : 00-59 |
| %f | 초 + 밀리 초 : SS.SSS |
| %s | 1970-01-01 이후의 초 수 |
| %J | 율리우스 일자 |
| %% | % 문자 그 자체 |
다른 날짜와 시간에 관한 함수는 strftime 함수를 사용하여 다음과 같이 나타낼 수 있다.
date(...) strftime('%Y-%m-%d', ...)
time(...) strftime('%H:%M:%S', ...)
datetime(...) strftime('%Y-%m-%d %H:%M:%S', ...)
julianday(...) strftime('%J', ...)
그러면 실제로 구해보도록 한다. 다음과 같이 SELECT 문을 실행한다. 처음에는 현재 시간을 밀리 초까지 얻을 수 있다.
select strftime('%H:%M:%f', 'now') as strftime;
sqlite> select strftime('%H:%M:%f', 'now') as strftime;
strftime
------------
15:16:57.814
sqlite>
다음은 문자를 결합하여 2019 년 1월 1일 이후의 초 수를 조회한다.
select strftime( ‘경과 시간(초) %s초’, ’now’) as strftime;
sqlite> select strftime( '경과 시간(초) %s초', 'now') as strftime;
strftime
--------------------
경과 시간(초) 1573053477초
sqlite>
Modifiers (한정자) 사용법
각 함수에는 인수 한정자를 하나 이상 지정할 수 있다.
date(timestring, modifier, modifier, ...)
time(timestring, modifier, modifier, ...)
datetime(timestring, modifier, modifier, ...)
julianday(timestring, modifier, modifier, ...)
strftime(format, timestring, modifier, modifier, ...)
지정 가능한 수식은 다음과 같다.
NNN years
NNN months
NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
start of year
start of month
start of day
weekday N
unixepoch
localtime
utc
그럼 순서대로 확인하자.
날짜 또는 시간 요소를 추가 감산
다음은 한정자는 대상의 날짜와 시간에 달과 날짜, 또한 시간과 분 등의 값을 더하거나 빼기를 할 경우에 사용한다.
NNN years
NNN months
NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
예를 들어, 1일 더하려면 ‘+1 days’라고 작성한다. 또한 4시간 되돌리려면 ‘-4 hours’로 작성한다. 수식을 여러개 지정할 수 있게에 1일 가산하여 4시간 되돌릴 수도 있다.
그러면 실제로 해보도록 한다, 다음과 같이 SELECT 문을 실행한다.
select datetime('2019-11-06', '+1 days') as datetime;
select datetime('2019-11-07', '-4 hours') as datetime;
select datetime('2019-11-06', '+2 days', '-4 hours') as datetime;
sqlite> select datetime('2019-11-06', '+1 days') as datetime;
datetime
-------------------
2019-11-07 00:00:00
sqlite> select datetime('2019-11-07', '-4 hours') as datetime;
datetime
-------------------
2019-11-06 20:00:00
sqlite> select datetime('2019-11-06', '+2 days', '-4 hours') as datetime;
datetime
-------------------
2019-11-07 20:00:00
sqlite>
더하기 및 빼기를 한 결과 일이나 달이 바뀌는 경우도 자동으로 계산된다.
첫날과 처음
다음 한정자는 대상의 날짜에 그 날짜의 연도의 첫날 등을 구한다.
start of year
start of month
start of day
‘start of year’를 지정하면 해당 날짜의 연도의 첫날 (1월 1일 00:00:00)이 반환된다. ‘start of month’로 지정하면 해당 날짜의 달의 첫날(1일 00:00:00)이 반환된다. ‘start of day’로 지정하면 해당 날짜의 자정 (00:00:00)이 반환된다.
그러면 실제로 해보도록 하자. 다음과 같이 SELECT 문을 실행한다.
select datetime('2019-11-07 21:16', 'start of year') as datetime;
select datetime('2019-11-07 21:16', 'start of month') as datetime;
select datetime('2019-11-07 21:16', 'start of day') as datetime;
sqlite> select datetime('2019-11-07 21:16', 'start of year') as datetime;
datetime
-------------------
2019-01-01 00:00:00
sqlite> select datetime('2019-11-07 21:16', 'start of month') as datetime;
datetime
-------------------
2019-11-01 00:00:00
sqlite> select datetime('2019-11-07 21:16', 'start of day') as datetime;
datetime
-------------------
2019-11-07 00:00:00
sqlite>
지정된 요일의 날
다음 한정자는 대상 날짜 이후의 날짜로 지정된 요일의 첫날을 반환한다.
weekday N
N은 요일을 나타내는 숫자를 지정한다. 0이 일요일 6이 토요일이다.
그러면 실제로 시도해 보도록 하자. 다음과 같이 SELECT 문을 실행한다.
select datetime ( '2019-11-07', 'weekday 0') as datetime;
select datetime ( '2019-11-07', 'weekday 3') as datetime;
sqlite> select datetime ( '2019-11-07', 'weekday 0') as datetime;
datetime
-------------------
2019-11-10 00:00:00
sqlite> select datetime ( '2019-11-07', 'weekday 3') as datetime;
datetime
-------------------
2019-11-13
UNIX 타임 스탬프 지정
다음 한정자를 지정하면 날짜 지정을 ‘DDDDDDDDDD’의 형식으로 지정했을 때 율리우스 일이 아니라 UNIX 타임 스탬프 값으로 처리한다.
unixepoch
UNIX 타임 스탬프는 1970 년 1월 1일 자정 이후의 초 수이다. 따라서 ‘DDDDDDDDDD’의 형식으로 지정한 값의 단위는 초로 처리된다.
그러면 실제로 해보도록 한다. 다음과 같이 SELECT 문을 실행한다.
select datetime('35') as datetime;
select datetime('35', 'unixepoch') as datetime;
sqlite> select datetime('35') as datetime;
datetime
--------------------
-4713-12-29 12:00:00
sqlite> select datetime('35', 'unixepoch') as datetime;
datetime
-------------------
1970-01-01 00:00:35
sqlite>
로컬 시간대로 표시
다음 한정자를 지정하면 해당 날짜를 현지 시간대로 변환한다.
localtime
기본적으로 대상의 날짜 및 시간 시간대는 UTC (세계 협정시)로 처리된다. localtime을 지정하면 UTC 시간대로 처리된 날짜와 시간을 로컬 시간대의 날짜와 시간으로 변환해 돌려준다.
한국의 경우는 KST (한국 표준시)로 변환되지만 KST는 UTC에 +09:00 한 것이다.
그러면 실제로 해보도록 한다. 다음과 같이 SELECT 문을 실행한다.
select datetime ( 'now') as utc, datetime ( 'now', 'localtime') as local;
sqlite> select datetime ( 'now') as utc, datetime ( 'now', 'localtime') as local;
utc local
------------------- -------------------
2019-11-06 15:42:56 2019-11-07 00:42:56
sqlite>
UTC 시간대로 표시
다음 한정자를 지정하면 해당 날짜를 UTC 시간대로 변환한다.
utc
‘utc’를 지정한 경우만 해당 날짜의 시간대를 현지 시간대로 취급한다. 그리고 대상의 날짜와 시간을 UTC 시간대로 변환해 돌려준다.
한국의 경우는 대상의 날짜 및 시간 시간대를 KST (한국 표준시)로 취급하고 UTC 시간대로 변환한다.
그러면 실제로 해보도록 한다. 다음과 같이 SELECT 문을 실행한다.
select datetime('2019-05-14 10:25') as utc, datetime('2019-05-14 10:25', 'utc') as local;
sqlite> select datetime('2019-05-14 10:25') as utc, datetime('2019-05-14 10:25', 'utc') as local;
utc local
------------------- -------------------
2019-05-14 10:25:00 2019-05-14 01:25:00
sqlite>