SQL에서 타임스탬프를 변환하는 방법

소개

SQL 데이터베이스의 타임스탬프 작업은 개발자와 DBA의 기본 기술입니다. 이 가이드에서는 주요 데이터베이스 플랫폼 전반의 변환 기능, 성능 고려 사항 및 일반적인 함정을 다룹니다.

빠른 참조

데이터베이스별 변환 기능

MySQL

-- Convert Unix timestamp to DATETIME
SELECT
  id,
  UNIX_TIMESTAMP(created_at) AS converted_date
FROM events;

-- Convert DATETIME to Unix timestamp
SELECT
  id,
  UNIX_TIMESTAMP(created_at) AS timestamp_value
FROM events;

-- Current timestamp in Unix format
SELECT UNIX_TIMESTAMP(NOW()) AS current_unix;

중요: MySQL TIMESTAMP에는 2038년 문제가 있습니다(범위: 1970-01-01 00:00:00 ~ 2038-01-19 03:14:07). 이 범위를 벗어나는 날짜에는 DATETIME 또는 BIGINT를 사용하세요.

포스트그레SQL

-- Convert Unix timestamp to TIMESTAMP WITH TIME ZONE
SELECT
  id,
  to_timestamp(event_timestamp) AS converted_date
FROM events;

-- Convert timestamp to TIMESTAMPTZ with timezone
SELECT
  id,
  event_timestamp AT TIME ZONE 'America/New_York' AS eastern_time
FROM events;

-- Current timestamp in Unix format
SELECT EXTRACT(EPOCH FROM NOW()) AS current_unix;

팁: PostgreSQL TIMESTAMP WITH TIME ZONE은 시간대를 인식합니다. 시간대를 지원하는 변환에는 AT TIME ZONE을 사용하세요.

SQL 서버(T-SQL)

-- Convert Unix timestamp to DATETIME2 (higher precision)
SELECT
  id,
  DATEADD(s, 1970-01-01 00:00:00, UNIX_TIMESTAMP(timestamp_column)) AS converted_date
FROM events;

-- Convert DATETIME2 to Unix timestamp
SELECT
  id,
  DATEDIFF(s, 1970-01-01 00:00:00, GETDATE()) AS unix_seconds
FROM events;

-- Current timestamp in Unix format
SELECT DATEDIFF(s, 1970-01-01 00:00:00, GETUTCDATE()) AS current_unix;

참고: DATETIME2는 더 높은 정밀도를 위해 분수 초(소수점 3자리)를 제공합니다. 정확한 계산을 위해서는 DATEDIFF/DATEADD 조합을 사용하세요.

SQL라이트

-- Convert Unix timestamp to DATETIME
SELECT
  id,
  datetime(timestamp_column, 'unixepoch') AS converted_date
FROM events;

-- Current timestamp in Unix format
SELECT strftime('%s', 'now') AS current_unix;

참고: SQLite는 기본적으로 Unix 시대를 사용합니다. 사용자 정의 형식에는 strftime 수정자를 사용하세요.

오라클

-- Convert Unix timestamp to DATE
SELECT
  id,
  TO_DATE('1970-01-01', 'YYYY-MM-DD') AS converted_date
FROM events;

-- Current timestamp
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS current_timestamp
FROM DUAL;

중요: Oracle TO_DATE 형식은 대소문자를 구분합니다. 항상 대문자 형식 지정자(YYYY, MM, DD 등)를 사용하세요.

실제 예

예 1: 변환 및 표시

-- MySQL: Convert and format in single query
SELECT
  id,
  event_name,
  DATE_FORMAT(UNIX_TIMESTAMP(created_at), '%Y-%m-%d %H:%i') AS formatted_date,
  UNIX_TIMESTAMP(created_at) AS unix_value
FROM events
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY created_at DESC;

예 2: 기간별 필터링

-- PostgreSQL: Efficient date range filtering with timestamp
SELECT
  id,
  event_name,
  event_timestamp AT TIME ZONE 'UTC' AS utc_time,
  TO_CHAR(event_timestamp AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI') AS formatted_date
FROM events
WHERE event_timestamp >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '7 days'))
  AND event_timestamp < EXTRACT(EPOCH FROM NOW())
ORDER BY event_timestamp DESC;

예 3: 여러 형식을 사용한 일괄 변환

-- SQL Server: Provide multiple timestamp formats in one query
SELECT
  id,
  event_name,
  event_timestamp,
  CONVERT(VARCHAR, event_timestamp, 120) AS iso8601,  -- Truncate to 120 chars
  CONVERT(DATETIME2, event_timestamp) AS datetime2,
  CONVERT(DATETIME, event_timestamp) AS readable_date,
  YEAR(event_timestamp) AS year,
  MONTH(event_timestamp) AS month,
  DAY(event_timestamp) AS day
FROM events
WHERE event_timestamp IS NOT NULL;

예시 4: 시차 계산

-- MySQL: Calculate time difference in seconds
SELECT
  id1, id2,
  timestamp1, timestamp2,
  TIMESTAMPDIFF(SECOND, timestamp2, timestamp1) AS diff_seconds,
  TIMESTAMPDIFF(DAY, timestamp2, timestamp1) AS diff_days,
  SEC_TO_TIME(TIMESTAMPDIFF(SECOND, timestamp2, timestamp1)) AS time_diff
FROM events
WHERE id IN (1, 2);

성능 최적화

지수 전략

타임스탬프 열의 기본 인덱스

모범 사례: 범위 쿼리 및 정렬 작업을 위해 항상 타임스탬프 열에 기본 인덱스를 생성하세요.

-- MySQL: Primary index for range queries
CREATE TABLE events (
  id INT PRIMARY KEY,
  event_name VARCHAR(255),
  event_timestamp TIMESTAMP,
  INDEX idx_timestamp (event_timestamp)
);

-- PostgreSQL: Partial index for range queries
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  event_name VARCHAR(255),
  event_timestamp TIMESTAMPTZ,
  INDEX idx_timestamp_range (event_timestamp)
);

-- SQL Server: Include timestamp in covering index
CREATE NONCLUSTERED INDEX idx_event_timestamp
ON events (event_timestamp DESC);

기능 인덱스

모범 사례: 날짜 그룹화 쿼리를 위해 계산 열(연도, 월, 일)에 기능 인덱스를 만듭니다.

-- MySQL: Functional index for date-based grouping
CREATE TABLE events (
  id INT PRIMARY KEY,
  event_name VARCHAR(255),
  event_timestamp TIMESTAMP,
  INDEX idx_year (YEAR(event_timestamp)),
  INDEX idx_month (MONTH(event_timestamp)),
  INDEX idx_day (DAY(event_timestamp))
);

-- PostgreSQL: Generated column for automatic year/month/day
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  event_name VARCHAR(255),
  event_timestamp TIMESTAMPTZ,
  event_year INTEGER GENERATED ALWAYS AS (EXTRACT(YEAR FROM event_timestamp)) STORED,
  event_month INTEGER GENERATED ALWAYS AS (EXTRACT(MONTH FROM event_timestamp)) STORED
);

쿼리 최적화

열에 대한 함수 호출 방지

일반적인 실수: 인덱싱된 열에 YEAR(), MONTH(), DAY()와 같은 함수를 사용하면 인덱스 사용이 방지됩니다.

-- BAD: Function call prevents index usage
SELECT id, event_name
FROM events
WHERE YEAR(event_timestamp) = 2024
AND MONTH(event_timestamp) = 6;

-- GOOD: Compare literal values to use index
SELECT id, event_name
FROM events
WHERE event_timestamp >= '2024-01-01 00:00:00'
  AND event_timestamp < '2024-07-01 00:00:00';

SARGABLE 가능 매개변수 사용

MySQL 팁: MySQL 8.0.18부터 준비된 문에 SARGABLE 매개변수를 사용하면 인덱스 사용이 허용됩니다.

-- MySQL: Create prepared statement for parameterized queries
PREPARE stmt FROM 'SELECT * FROM events WHERE event_timestamp >= ? AND event_timestamp < ?';

-- Execute with parameters (efficient)
EXECUTE stmt USING @start_ts, @end_ts;

결과 세트 제한

모범 사례: 필요한 데이터만 반환하려면 범위 쿼리와 함께 항상 LIMIT를 사용하세요.

-- MySQL: Paginate large datasets
SELECT id, event_name, event_timestamp
FROM events
WHERE event_timestamp >= UNIX_TIMESTAMP('2025-01-01')
ORDER BY event_timestamp ASC
LIMIT 1000;

-- PostgreSQL: Use cursor-based pagination for large result sets
DECLARE cursor CURSOR FOR
  SELECT id, event_name, event_timestamp
  FROM events
  WHERE event_timestamp >= '2025-01-01 00:00:00 UTC'
  ORDER BY event_timestamp ASC;

일반적인 함정

시간대 처리

MySQL 시간대 함수

중요: MySQL TIMESTAMP는 시간대 정보를 저장하지 않습니다. 시간대 인식 저장을 위해 TIMESTAMP WITH TIME ZONE을 사용하거나 별도의 시간대 열과 함께 DATETIME을 사용하세요.

-- Convert to specific timezone
SELECT
  id,
  event_name,
  event_timestamp AT TIME ZONE 'America/Los_Angeles' AS la_time,
  event_timestamp AT TIME ZONE 'America/New_York' AS ny_time
FROM events;

-- Get current time in specific timezone
SELECT NOW() AS la_current, CONVERT_TZ(UTC, 'America/New_York') AS ny_current;

대안: 다중 시간대 애플리케이션을 위해 UTC 타임스탬프와 별도의 시간대 열을 저장합니다.

데이터 무결성

타임스탬프 일관성 보장

-- MySQL: Add CHECK constraint for reasonable timestamp range
CREATE TABLE events (
  id INT PRIMARY KEY,
  event_timestamp TIMESTAMP NOT NULL,
  CHECK (event_timestamp BETWEEN
    UNIX_TIMESTAMP('1970-01-01') AND
    UNIX_TIMESTAMP('2038-01-19')
  )
);

-- PostgreSQL: Use EXCLUDE constraint to prevent invalid data
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  event_timestamp TIMESTAMPTZ NOT NULL,
  EXCLUDE (
    event_timestamp < EXTRACT(EPOCH FROM TIMESTAMP '1970-01-01')
    OR event_timestamp > EXTRACT(EPOCH FROM TIMESTAMP '2038-01-19')
  )
);

마이그레이션 전략

증분 마이그레이션

전략: 대규모 테이블의 경우 장기 실행 트랜잭션을 방지하려면 타임스탬프 기반 WHERE 절을 사용하여 일괄 마이그레이션하세요.

-- Process 1000 rows at a time, ordered by timestamp
UPDATE events
SET last_processed = 1
WHERE event_timestamp < (
  SELECT event_timestamp
  FROM events
  WHERE last_processed = 0
  ORDER BY event_timestamp ASC
  LIMIT 1000
);

-- Continue until all rows processed
-- Repeat until all events marked as processed

고급 패턴

에포크 타임스탬프 처리

데이터베이스별 패턴

MySQL 패턴

마이크로초 정밀도의 Unix 타임스탬프

-- MySQL: Using BIGINT for millisecond timestamps
CREATE TABLE high_precision_events (
  id BIGINT PRIMARY KEY,
  event_timestamp BIGINT,  -- Milliseconds since epoch
  event_micros INT(3),  -- Microseconds (0-999)
  INDEX idx_timestamp (event_timestamp DESC)
);

-- Convert millisecond timestamp to human-readable format
SELECT
  id,
  FROM_UNIXTIME(event_timestamp / 1000) AS seconds,
  DATE_FORMAT(FROM_UNIXTIME(event_timestamp / 1000), '%Y-%m-%d %H:%i:%s') AS formatted
FROM high_precision_events;

PostgreSQL 패턴

날짜 구성요소에 EXTRACT 사용

-- PostgreSQL: Extract date components for filtering
SELECT
  id,
  event_name,
  EXTRACT(YEAR FROM event_timestamp) AS year,
  EXTRACT(MONTH FROM event_timestamp) AS month,
  EXTRACT(DAY FROM event_timestamp) AS day,
  DATE_TRUNC('day', event_timestamp) AS date_only
FROM events
WHERE event_name = 'Daily Backup';

모범 사례 요약

성능 체크리스트

✅ Use appropriate data types (TIMESTAMP vs DATETIME)
✅ Create indexes on timestamp columns
✅ Use SARGABLE-able parameters when possible
✅ Limit result sets with pagination
✅ Avoid function calls on indexed columns
✅ Use WHERE clauses on timestamp for range queries
✅ Consider generated columns for date filtering
✅ Validate timestamp ranges on input
✅ Handle timezones explicitly (don't rely on implicit conversion)
✅ Use CHECK/EXCLUDE constraints for data integrity
❌ Don't store both timestamp and datetime for same data
❌ Don't use VARCHAR for timestamp columns
❌ Don't convert timestamp to string for every query

대용량 타임스탬프 애플리케이션(>1M 행)의 경우 적절한 인덱싱 및 쿼리 패턴을 사용하면 쿼리 시간을 50-80%까지 줄일 수 있습니다.

관련 도구

• 타임스탬프 형식 변환기 - 여러 타임스탬프 형식 간 변환
• Unix 타임스탬프 변환기 - Unix 타임스탬프를 날짜로 변환
• 현재 타임스탬프 - 현재 타임스탬프를 여러 형식으로 가져옵니다.
• 타임스탬프 유효성 검사기 - 타임스탬프 형식 및 범위 유효성 검사