SQLite User Guide for Android, iPhone - DateTime 함수, DateTime Formatting

SQLite Databae에 대한 여섯번째 포스트입니다. 이번 포스트에서는 SQLite의 DateTime 관련 함수 및 Formatting 에 대해서 다뤄보도록 하겠습니다. 이 부분은 다른 DBMS와 많이 다르고 자주 사용하는 함수들이라 몇가지 표현법은 꼭 익혀야 될 점인것 같습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)


1. DateTime 관련 Keyword

먼저 SQLite에서는 DateTime관련해서 다음과 같은 세가지 키워드가 있습니다.
CURRENT_TIME : 현재 시간 (형식: 03:22:56) 다만, UTC 기준입니다.
CURRENT_DATE: 현재 날짜 (형식: 2010-08-25) UTC 기준.
CURRENT_DATETIME : 현재 날자 및 시간 (형식: 2010-08-25 03:23:37) UTC 기준.

UTC 기준 날짜 및 시간이라서 사용할때는 로컬시간으로 다시 변환작업이 필요할 듯 보입니다. DATETIME 관련 함수들이 있어 크게 유용해 보이지는 않습니다.

2. DateTime 관련 함수.

DateTime 관련 함수는 다음의 5가지가 있습니다.

date(timestring, modifier, modifier, ...)  : 날짜 함수
time(timestring, modifier, modifier, ...)  : 시간 함수
datetime(timestring, modifier, modifier, ...)  :날짜/시간 함수
julianday(timestring, modifier, modifier, ...)  : 율리우스력 함수
strftime(format, timestring, modifier, modifier, ...)  : 날짜 포맷팅 함수


위 다섯개의 함수 인자 중 timestring 파라미터에로 들어갈 수 있는 날짜 형식은 아래와 같습니다.

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

strftime 함수에 사용되는 format에 사용할 수 있는 값들은 다음과 같습니다.

%d    day of month: 00
%f     fractional seconds: SS.SSS
%H    hour: 00-24
%j     day of year: 001-366
%J     Julian day number
%m    month: 01-12
%M    minute: 00-59
%s     seconds since 1970-01-01
%S     seconds: 00-59
%w     day of week 0-6 with sunday==0
%W     week of year: 00-53
%Y     year: 0000-9999
%%    %

함수의 modifer에 들어갈 수 있는 값들은 다음과 같습니다.

1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. start of month
8. start of year
9. start of day
10. weekday N
11. unixepoch
12. localtime
13. utc


잘 안 와 닿는듯 합니다. 아래 샘플 코드를 보면 이해가 가실 겁니다.

--UTC 기준의 현재 날짜/시간
select datetime('now');
2010-08-25 04:01:46

-- 로컬 기준의 현재 날짜/시간
select datetime('now','localtime');
2010-08-25 13:02:30

--현재 로컬 기준 시간에서 10분 3.5초를 더한 시간.
select datetime('now','localtime','+3.5 seconds','+10 minutes');
2010-08-25 13:14:15

--현재 로컬 시간에 3.5초를 더하고 날짜는 돌아오는 화요일 (weekday == 0 이 일요일입니다.)
select datetime('now','localtime','+3.5 seconds','weekday 2');
2010-08-31 13:05:39

--현재 달의 마지막 날짜
SELECT date('now','start of month','+1 month','-1 day','localtime');
2010-08-31

--2004-01-01 02:34:56초부터 현재까지의 총 초
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
209785028

--현재날짜/시간 기준에서 올해 9번째달의 첫번째 화요일
SELECT date('now','start of year','+9 months','weekday 2');
2010-10-05

-- 날짜 포맷 스타일 변경
select strftime("%Y/%m/%d %H:%M:%S",'now','localtime');
2010/08/27 09:17:22