▲

GN⁺: HN 공개: SQLite에서 고정밀 날짜/시간 기능

 (antonz.org)
2P by neo 3달전 | favorite | 댓글 1개

고정밀 날짜/시간 확장 기능

SQLite는 기본적인 날짜 함수들을 제공하지만, 더 많은 기능이 필요하여 sqlean-time이라는 고정밀 날짜/시간 확장 기능을 만들었음. 이 확장은 구조화된 API와 다양한 기능을 제공함.

참고. SQLite에 확장을 추가하는 것은 매우 간단함. 파일을 다운로드하고 하나의 데이터베이스 명령을 실행하면 됨.

개념

이 확장은 두 가지 값 유형을 사용함: 시간(Time)과 기간(Duration).

  • 시간(Time): 초와 나노초로 구성된 쌍으로, 0시간(0001-01-01 00:00:00 UTC) 이후의 초와 현재 초 내의 나노초를 나타냄.

    • 내부 표현으로 시간을 저장할 수 있으며, 이는 수십억 년 전후의 날짜를 나노초 정밀도로 표현할 수 있음.
    • Unix epoch(1970-01-01 00:00:00 UTC) 이후의 초(밀리초, 마이크로초, 나노초)로 시간을 저장할 수도 있음.
    • 시간은 항상 UTC로 저장되고 작동하지만 특정 시간대 오프셋으로 변환 가능.

  • 기간(Duration): 나노초 단위의 64비트 숫자로, 약 290년까지의 값을 나타낼 수 있음.

시간 값 생성

  • 현재 시간:

    select time_fmt_iso(time_now());  -- 2024-08-06T21:22:15.431295000Z

  • 특정 날짜/시간:

    select time_fmt_iso(time_date(2011, 11, 18));  -- 2011-11-18T00:00:00Z
select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35));  -- 2011-11-18T15:56:35Z

시간 필드 추출

다양한 날짜/시간 필드를 추출하는 함수들이 있음:

select 'year  = ' || time_get_year(time_now());
select 'month  = ' || time_get_month(time_now());
select 'day   = ' || time_get_day(time_now());

Unix 시간

Unix 시간(1970-01-01 UTC 이후의 시간)으로부터 시간 값을 생성하는 함수들:

select time_fmt_iso(time_unix(1321631795));  -- 2011-11-18T15:56:35Z

시간 값을 Unix 시간으로 변환하는 함수들:

select time_to_unix(time_now());  -- 1722979335

시간 비교

시간 값을 비교하는 함수들:

select time_after(time_now(), time_date(2011, 11, 18));  -- 1
select time_before(time_now(), time_date(2011, 11, 18));  -- 0

시간 연산

기간을 시간 값에 더하는 함수들:

select time_fmt_iso(time_add(time_now(), 24*dur_h()));  -- 2024-08-07T21:22:15.431295000Z

기간 상수:

  • dur_us() - 1 마이크로초
  • dur_ms() - 1 밀리초
  • dur_s() - 1 초
  • dur_m() - 1 분
  • dur_h() - 1 시간

반올림

지정된 필드의 정밀도로 시간 값을 반올림하는 함수들:

select 'original  = ' || time_fmt_iso(t.v) from t union all
select 'millennium = ' || time_fmt_iso(time_trunc(t.v, 'millennium')) from t;

포맷팅

ISO 8601 시간 문자열을 반환하는 함수들:

select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35, 666777888), 3*3600);  -- 2011-11-18T18:56:35.666777888+03:00

기간 상수

일반적인 기간을 나노초로 반환하는 함수들:

select dur_ns();  -- 1
select dur_us();  -- 1000

감사

이 확장은 C로 구현되었으며, Go의 표준 라이브러리 시간 패키지(BSD 3-Clause License)를 기반으로 설계 및 구현되었음.

설치 및 사용법

  1. 최신 릴리스를 다운로드
  2. SQLite 명령줄 인터페이스에서 사용: 
    sqlite> .load ./time
sqlite> select time_now();

GN⁺의 정리

  • sqlean-time 확장은 SQLite에 고정밀 날짜/시간 기능을 추가하여 다양한 시간 연산을 가능하게 함.
  • 시간과 기간을 나노초 단위로 처리할 수 있어 매우 정밀한 시간 계산이 가능함.
  • 다양한 시간 포맷팅 및 비교 기능을 제공하여 개발자들이 쉽게 사용할 수 있음.
  • SQLite의 기본 날짜 함수보다 훨씬 더 많은 기능을 제공하여 복잡한 시간 연산을 필요로 하는 프로젝트에 유용함.
▲
neo 3달전  [-]
Hacker News 의견

  • Jon Skeet가 문서화한 시간대 변경 및 지역 시간 불연속성의 특별한 경우를 처리하는지에 대한 질문

  • 날짜/시간 및 암호화 라이브러리를 직접 구축하지 않는 것이 좋음

    • 끝없는 엣지 케이스가 문제를 일으킬 수 있음
    • 새로운 라이브러리를 접할 때 회의적인 이유

  • 세 가지 다른 시간 표현/크기가 흥미로움

    • 나노초 정밀도가 수십억 년의 기간 동안 필요한 사용 사례가 궁금함
    • 나노초 정밀도로 ±290년 범위만 제공되는 것이 혼란스러움

  • 부호 있는 정수를 사용하는지 여부를 명확히 하는 것이 중요함

    • 문서를 읽어보면 부호 있는 정수일 수도 있고 아닐 수도 있음
    • 부호 있는 정수라면 동일한 날짜와 시간을 나타내는 여러 비트 문자열이 있을 수 있음

  • SQLite3에 확장 가능한 타입 시스템이 있었으면 좋겠음

  • SQLite의 중요한 누락된 기능을 언급하며 매우 멋지다고 평가함

  • 데이터베이스가 단위를 추적해야 한다고 주장함

    • 예를 들어, 시간 열이 float64 초 단위로 나타내는 것을 명시할 수 있어야 함
    • 데이터베이스가 "2h"를 7200.0초로 변환하고 테이블 스캔 중에 비교할 수 있어야 함
    • 과거에 이런 단위 처리를 하는 특수 목적 SQL 데이터베이스를 작성했지만, 이후로 본 적이 없음
    • 시간뿐만 아니라 질량, 부피, 정보, 온도 등 모든 단위를 처리할 수 있어야 함
    • 수학적 오류를 조기에 잡을 수 있도록 데이터베이스가 수학적 무의미한 연산을 거부하도록 가르칠 수 있음

  • 나노초 표현과 나노 범위 외의 연도 중 어느 것이 더 유용한지에 대한 질문

    • "정확한" 과학을 하지 않기 때문에 나노초의 가치는 제한적임
    • 역사적 날짜를 나타낼 수 있는 것이 더 자주 필요할 것 같음

  • golang 스타일의 유닉스 타임스탬프를 나노초 단위로, 부호 있는 int64로 사용하는 것을 제안함

    • 나노초 정밀도로 수백만 년을 커버할 수 없을 수도 있지만, 정말 필요한지 의문임

  • "epoch 이후 초"라는 표현을 정확히 의미하지 않는 한 사용하지 말아야 한다고 주장함

    • 예시 쿼리: select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18));
답변달기