SQLite 날짜 시간 비교

1. DateTime 데이터 유형으로 두 날짜 비교
2. strftime() 함수를 사용하여 SQLite에서 두 날짜 비교

SQLite 데이터베이스는 정수, 문자열 및 일부 날짜-시간 형식을 포함하여 다양한 유형의 데이터를 사용하여 저장합니다. 날짜-시간 저장에는 date, smalldatetime, DateTime, datetime2 등과 같은 다양한 유형이 있습니다.

일반적으로 SQLite 데이터베이스에서 비교를 위해 WHERE 절을 사용하고 필요한 데이터를 검색합니다.

문제는 SQL 데이터베이스 내에서 다양한 유형의 날짜/시간 형식을 비교하는 방법입니다. 날짜와 시간을 직접 비교할 수 있는 방법은 없습니다.

DateTime 데이터 유형으로 두 날짜 비교

SQLite에는 날짜를 직접 비교할 수 있는 방법이 없습니다. 그러나 SQLite에는 날짜와 시간에 대한 내장 함수가 있어 날짜와 시간을 텍스트, 정수 또는 실제 값으로 저장할 수 있습니다.

응용 프로그램에 따라 다르며 내장된 시간 및 날짜 함수의 형식을 선택할 수 있습니다.

1. 텍스트는 YYYY-MM-DD HH:MM:SS.SSS 형식을 갖는 ISO8601 문자열을 사용합니다.
2. 정수는 1970-01-01 00:00:00 UTC 형식의 Unix 시간을 사용합니다.

따라서 비교를 위해 일반적으로 날짜와 시간을 특정 형식의 문자열로 변환하여 일관성을 유지합니다. 예를 들어 YYYY-MM-DD hh:mm:ss 형식으로 날짜를 저장합니다.

일관되게 이 형식으로 날짜와 시간을 저장하면 날짜와 시간을 쉽게 비교할 수 있습니다. 아래 설명을 따라 간단한 비교 예를 날짜와 함께 보여줄 수 있습니다.

SELECT * FROM name_of_the_table WHERE comparsion_date < '2020-04-05 00:00:00';

비교가 숫자가 아님을 알 수 있지만 알파벳 비교가 있습니다. 저장된 날짜 형식과 비교 날짜 형식이 비슷한 경우에 맞습니다.

여기서 우리는 두 가지 중요한 점을 기억해야 합니다.

1. 비교하기 쉽도록 24시간 형식으로 시간을 저장하는 것이 좋습니다.
2. 날짜와 시간을 하나의 열에 저장할 필요는 없지만 중요한 요소는 형식의 일관성입니다. 날짜와 시간이 별도의 열에 있는 경우 형식은 날짜 열의 경우 YYYY-MM-DD이고 시간 열의 경우 hh:mm:ss여야 합니다.

따라서 날짜와 시간이 별도의 열에 있으면 다음과 같이 SQL 쿼리를 작성합니다. 요점은 데이터베이스 전체에서 일관적인 날짜 및 시간 형식을 만드는 것입니다.

SELECT * FROM name_of_the_table WHERE comparsion_date = '2020-05-03' AND comparison_time = '08:03:11';

YYYY-MM-DD hh:mm:ss 형식이 선호되는 또 다른 이유는 가장 높은 인덱스에서 가장 낮은 인덱스로, 즉 시간에서 초로 정렬할 때 비교가 매우 쉽고 성능이 우수하기 때문입니다. 저하되지 않습니다.

strftime() 함수를 사용하여 SQLite에서 두 날짜 비교

SQLite에서는 strftime() 함수를 사용하여 인수에 지정된 형식으로 날짜를 반환합니다. 두 번째 인수는 지정된 형식으로 시간 문자열을 반환합니다. 나머지는 다른 결과로 데이터를 가져오기 위한 수정자입니다.

strftime() 함수의 구문은 다음과 같습니다.

strftime(date_format, time_string, remaining_modifiers, ...)

strftime() 함수를 사용하여 날짜와 시간을 비교하기 위해 SQL 문에서 BETWEEN 및 AND 연산자를 사용하여 특정 기간의 특정 날짜를 찾습니다.

SELECT * FROM name_of_the_table WHERE
    strftime('%s', c_date) BETWEEN strftime('%s', start_date) AND strftime('%s', end_date)

strftime() 함수를 사용하여 현재 시간에서 지정된 날짜 시간까지의 두 datetime 간의 차이를 출력할 수 있습니다.

SELECT strftime('%s','now') - strftime('%s','2019-09-05 03:52:35');

출력:

Output of strftime('%s','now') - strftime('%s','2019-09-05 03:52:35')
-----------------------------------------------------------
2010032

DateTime을 시, 분, 초 및 밀리초로 변환

strftime() 함수를 사용하여 DateTime 단위를 시, 분, 초 및 밀리초로 변환합니다. 다음 결과를 얻으려면 strftime() 함수를 사용해야 합니다.

SELECT strftime('%H %M %S %s','now');

출력:

Output of strftime('%H %M %S %s','now')
-----------------------------
16 54 04 2541572981

지정된 시간부터 현재 시간(지금)까지의 초 계산

또한 strftime() 함수를 사용하여 DateTime 데이터 유형을 초로 변환하고 제공된 인수에서 현재 시간까지 남은 총 초를 초 단위로 계산합니다.

SELECT strftime('%s','now') - strftime('%s','2019-01-04 01:25:47');

출력:

Output of strftime('%s','now') - strftime('%s','2019-01-04 01:25:47')
-----------------------------------------------------------
3221184

년, 월, 일의 시간 기록 계산

작업 시작부터 종료 날짜까지의 테이블이 있고 사용자가 연, 월, 일 단위로 총 시간 기록을 계산하려는 작업 유지 관리 기록의 간단한 예를 들어 보겠습니다.

다음은 emp_id, start_job_date 및 end_job_date의 세 열이 있는 테이블 형식입니다. emp_id의 데이터 유형은 정수이고 나머지 start_job_date 및 end_job_date 열은 DateTime입니다.

SQL 쿼리를 사용하여 위의 테이블에서 start_job_date와 end_job_date의 차이를 계산하기 위해 strftime() 함수를 사용하여 년, 월, 일의 차이를 직접 구합니다.

SELECT strftime('%Y',end_job_date - start_job_date) as "Difference in years",
strftime('%m',end_job_date - start_job_date) as "Difference in months",
strftime('%d',end_date - start_job_date) as "Difference in days"
FROM job_history;

출력: