SQLite3_group_concat, 날짜-시간 함수

1. group_concat 함수

그룹 내의 컬럼의 값을 연결하여 표시하는 데 사용

group_concat(컬럼명, 구분 문자)

- 지정한 컬럼의 값 중에 null이 아닌 값을 하나의 문자열로 연결한 결과를 출력

- group by절을 사용하면 그룹마다 연결한 결과 출력

- 구분 문자를 지정하지 않은 경우 기본값은 쉼표(,)를 구분자로 값을 연결 

먼저 user테이블을 없애고 다시 생성하여 데이터를 추가했습니다.

그리고 name컬럼을 group_concat 해보았더니 결과로 기본 구분자 쉼표로 출력되었습니다. 마지막 null값은 출력되지 않았습니다.

이번엔 다른 구분자 / 를 사용해봅시다

group by절을 사용하여 gender컬럼 별로 그룹화해서 연결해봅시다.

2. 날짜와 시간 함수

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함수, time함수, datetime함수는 오른쪽 형식으로 반환하고 julianday함수는 율리우스 날짜를 반환합니다.

strftime함수는 형식을 직접 입력합니다

 

주의사항: 지정된 날짜와 시간은 시간대로 UTC로 처리 

 

함수 하나하나 예제로 다뤄봅시다.

2-1 date함수, time함수, datetime함수

이번에는 now를 지정하여 현재 날짜와 시간을 출력해봅시다.

지금 현재시간은 오후 3시쯤인데 시간대는 UTC로 나오는 것을 확인할 수 있다.

 

2-2 julianday 함수

: 인수에 지정된 날짜와 시간을 율리우스 날짜로 가져옵니다. 율리우스 날짜는 기원전 4714년 11월 24일 정오를 기준일로 하여 기준일부터 일차를 나타냅니다. 단위는 일이고 기준일은 정오가 되고 있는 점에 주의해야 합니다.

 

날짜	율리우스 날짜
기원전 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초

그럼 실제로 율리우스 날짜를 출력해봅시다.

3. 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	율리우스 일자
%%	% 문자

그러면 date함수를 strftime함수로 바꾸면 strftimd('%Y-%m-%d', ... )으로 사용할 수 있다.

 

예제를 통하여 strftime함수를 해보겠습니다.

위에는 현재 시간을 나타냅니다. 다음은 문자를 결합하여 2019년 1월 1일 이후의 초수를 조회합니다.

4. 날짜 또는 시간 추가 또는 감산

4-1 하루를 더하기 +1 days 

4-2 4시간 빼기 -4 hours

4-3 2일 더하고 4시간 빼기 

'2021-10-06'에서 2일을 더하면 '2021-10-08 24:00:00'이다. 여기서 4시간 전으로 가면 '2021-10-07 20:00:00'이 됩니다.

 

여기서 주의해야 되는 점은 숫자 뒤에 days나 hours를 할 때 한 칸 띄어쓰기에 주의하면 좋습니다.

 

5. 첫날과 처음

start of year
start of month
start of day

위와 같은 한정자는 대상의 날짜의 연도의 첫날 등을 구합니다.

예제를 통해 바로 이해해봅시다.

맨 첫 번째 줄은 연도의 처음인 2021년도의 처음인 1월 1일이 출력이 되었고

두 번째 줄은 달의 처음인 2021년도 10월 1일이 출력이 되었고

세 번째 줄은 날의 처음인 2021년 10월 6일은 00시 00분 00초가 출력이 되었습니다.

 

6.  지정된 요일의 날

 weekday N

N은 요일을 나타내는 숫자이다. 요일은 아래와 같이 표현된다.

0: 일요일, 1: 월요일, 2: 화요일, 3: 수요일, 4: 목요일, 5: 금요일, 6: 토요일이다.

예제를 통해 이해해 봅시다.

10월 6일이 있는 주의 일요일은 10월 10일이고 수요일은 10월 6일이란 것을 알 수 있습니다.

 

7. 로컬 시간대로 표시

localtime

위에서 시간을 출력할 때, UTC기준으로 시간이 출력된다고 했습니다. 이를 KST(한국 표준시)로 변환하려면 localtime인수에 써주시면 됩니다. ( KST=UTC+09:00 )

실행한 결과 utc와 local 시간 차이가 9시간이 난다는 것을 알 수 있습니다.

 

 

다음 게시글에서는 문자열에서 사용할 수 있는 여러 가지 함수를 알아보도록 하겠습니다.