[PostgreSQL] λ‚ μ§œ λ˜λŠ” μ‹œκ°„μ˜ 일뢀λ₯Ό μ§€μ •λœ λ‹¨μœ„λ‘œ 자λ₯΄κΈ° | 'date_trunc' ν•¨μˆ˜ μ„€λͺ…

2023. 12. 3. 16:54Β·πŸ’» Programming/Database
λ°˜μ‘ν˜•


PostgreSQLμ—μ„œ date_trunc ν•¨μˆ˜λŠ” λ‚ μ§œ λ˜λŠ” μ‹œκ°„μ˜ 일뢀λ₯Ό μ§€μ •λœ λ‹¨μœ„λ‘œ 자λ₯΄λŠ” 데 μ‚¬μš©λœλ‹€. 이 ν•¨μˆ˜λŠ” μ£Όμ–΄μ§„ λ‹¨μœ„μ— 따라 μ£Όμ–΄μ§„ λ‚ μ§œ λ˜λŠ” μ‹œκ°„μ˜ 값을 반올림 λ˜λŠ” λ²„λ¦Όν•˜μ—¬ λ°˜ν™˜ν•œλ‹€.

 

date_trunc(unit, source);

 

  • unit: 자λ₯΄κ³ μž ν•˜λŠ” μ‹œκ°„ λ˜λŠ” λ‚ μ§œμ˜ λ‹¨μœ„λ₯Ό μ§€μ •
  • source: 자λ₯΄κ³ μž ν•˜λŠ” λŒ€μƒμ΄ λ˜λŠ” λ‚ μ§œ λ˜λŠ” μ‹œκ°„

 

 

date_trunc μ‚¬μš© μ˜ˆμ‹œ

 

ν˜„μž¬ λ‚ μ§œμ™€ μ‹œκ°„μ—μ„œ μ‹œκ°„μ„ 자λ₯΄κΈ°

SELECT date_trunc('hour', NOW());
-- κ²°κ³Ό: ν˜„μž¬ μ‹œκ°„μ˜ μ •κ°κΉŒμ§€μ˜ 값이 λ°˜ν™˜λ©λ‹ˆλ‹€.

 

 

λ‚ μ§œμ—μ„œ 월을 자λ₯΄κΈ°

SELECT date_trunc('month', '2023-03-15'::date);
-- κ²°κ³Ό: '2023-03-01' (ν•΄λ‹Ή μ›”μ˜ 첫 번째 λ‚ μ§œ)이 λ°˜ν™˜λ©λ‹ˆλ‹€.

 

 

μ‹œκ°„μ—μ„œ 뢄을 자λ₯΄κΈ°

SELECT date_trunc('minute', '2023-03-15 12:34:56'::timestamp);
-- κ²°κ³Ό: '2023-03-15 12:34:00'이 λ°˜ν™˜λ©λ‹ˆλ‹€.

 

 

ν˜„μž¬ λ‚ μ§œμ—μ„œ 년을 자λ₯΄κΈ°

SELECT date_trunc('year', CURRENT_DATE);
-- κ²°κ³Ό: ν˜„μž¬ μ—°λ„μ˜ 첫 번째 λ‚ μ§œκ°€ λ°˜ν™˜λ©λ‹ˆλ‹€.

 

date_trunc ν•¨μˆ˜λ₯Ό μ‚¬μš©ν•˜μ—¬ μ›ν•˜λŠ” μ‹œκ°„ λ˜λŠ” λ‚ μ§œμ˜ 정밀도λ₯Ό μ‘°μ ˆν•  수 있고, ν•¨μˆ˜μ˜ 두 번째 맀개 λ³€μˆ˜λ‘œ μ‚¬μš©λ˜λŠ” sourceλŠ” timestamp, date, timestamptz λ“±μ˜ ν˜•μ‹μ΄ 될 수 μžˆλ‹€.

 

 

λ‚ μ§œλ³„ 데이터 κ°€μ Έμ˜€κΈ°

κ°œμΈμ μœΌλ‘œλŠ” νŠΉμ • DB ν…Œμ΄λΈ”μ— API 호좜 결과와 ν•΄λ‹Ή μ‹œκ°„μ„ μ €μž₯ν•˜κ³  μžˆμ„ λ•Œ, λ‚ μ§œλ³„ API ν˜ΈμΆœλŸ‰μ„ μΆ”μΆœν•˜κΈ° μœ„ν•΄ date_trunc ν•¨μˆ˜λ₯Ό μ‚¬μš©ν–ˆλ‹€.

SELECT
  date_trunc('day', TO_DATE(data_column, 'YYYY-MM-DD HH24:MI:SS')) AS day,
  COUNT(*) AS count
FROM
  schemas.table
GROUP BY
  date_trunc('day', TO_DATE(data_column, 'YYYY-MM-DD HH24:MI:SS'))
ORDER BY
  day;

 

μœ„μ™€ 같은 쿼리λ₯Ό μž‘μ„±ν•˜λŠ” 경우 schemas.tableμ—μ„œ λ‚ μ§œλ₯Ό μ €μž₯ν•˜λŠ” data_column μ»¬λŸΌμ„ κΈ°μ€€μœΌλ‘œ ν•˜λ£¨ λ§ˆλ‹€ μ €μž₯된 λ°μ΄ν„°μ˜ 개수λ₯Ό μΆ”μΆœν•  수 μžˆλ‹€. μ•„λž˜λŠ” μΆ”μΆœλœ 결과이닀.

 

λ°˜μ‘ν˜•

'πŸ’» Programming > Database' μΉ΄ν…Œκ³ λ¦¬μ˜ λ‹€λ₯Έ κΈ€

[PostgreSQL/Python] DB νŠΈλžœμž­μ…˜ 비동기 μ²˜λ¦¬ν•˜κΈ° | asyncio, asyncpg | λ°μ΄ν„°λ² μ΄μŠ€ μ—°κ²° ν’€ λ§Œλ“€κΈ°  (1) 2023.11.03
[PostgreSQL/Python] psycopg2 : PostgreSQL λ°μ΄ν„°λ² μ΄μŠ€μ— μ ‘μ†ν•˜κ³  μƒν˜Έ μž‘μš©ν•˜κΈ°  (0) 2023.06.29
'πŸ’» Programming/Database' μΉ΄ν…Œκ³ λ¦¬μ˜ λ‹€λ₯Έ κΈ€
  • [PostgreSQL/Python] DB νŠΈλžœμž­μ…˜ 비동기 μ²˜λ¦¬ν•˜κΈ° | asyncio, asyncpg | λ°μ΄ν„°λ² μ΄μŠ€ μ—°κ²° ν’€ λ§Œλ“€κΈ°
  • [PostgreSQL/Python] psycopg2 : PostgreSQL λ°μ΄ν„°λ² μ΄μŠ€μ— μ ‘μ†ν•˜κ³  μƒν˜Έ μž‘μš©ν•˜κΈ°
뭅즀
뭅즀
AI 기술 λΈ”λ‘œκ·Έ
    λ°˜μ‘ν˜•
  • 뭅즀
    CV DOODLE
    뭅즀
  • 전체
    였늘
    μ–΄μ œ
  • 곡지사항

    • ✨ About Me
    • λΆ„λ₯˜ 전체보기 (198)
      • πŸ“– Fundamentals (33)
        • Computer Vision (9)
        • 3D vision & Graphics (6)
        • AI & ML (15)
        • NLP (2)
        • etc. (1)
      • πŸ› Research (64)
        • Deep Learning (7)
        • Image Classification (2)
        • Detection & Segmentation (17)
        • OCR (7)
        • Multi-modal (4)
        • Generative AI (6)
        • 3D Vision (2)
        • Material & Texture Recognit.. (8)
        • NLP & LLM (11)
        • etc. (0)
      • 🌟 AI & ML Tech (7)
        • AI & ML μΈμ‚¬μ΄νŠΈ (7)
      • πŸ’» Programming (85)
        • Python (18)
        • Computer Vision (12)
        • LLM (4)
        • AI & ML (17)
        • Database (3)
        • Apache Airflow (6)
        • Docker & Kubernetes (14)
        • μ½”λ”© ν…ŒμŠ€νŠΈ (4)
        • C++ (1)
        • etc. (6)
      • πŸ’¬ ETC (3)
        • μ±… 리뷰 (3)
  • 링크

  • 인기 κΈ€

  • νƒœκ·Έ

    Python
    ChatGPT
    material recognition
    3D Vision
    CNN
    segmentation
    Computer Vision
    λ”₯λŸ¬λ‹
    κ°μ²΄κ²€μΆœ
    pytorch
    도컀
    컴퓨터비전
    VLP
    nlp
    AI
    multi-modal
    deep learning
    OpenCV
    GPT
    Image Classification
    파이썬
    object detection
    ν”„λ‘¬ν”„νŠΈμ—”μ§€λ‹ˆμ–΄λ§
    LLM
    OCR
    OpenAI
    객체 κ²€μΆœ
    airflow
    Text recognition
    pandas
  • 졜근 λŒ“κΈ€

  • 졜근 κΈ€

  • hELLOΒ· Designed Byμ •μƒμš°.v4.10.3
뭅즀
[PostgreSQL] λ‚ μ§œ λ˜λŠ” μ‹œκ°„μ˜ 일뢀λ₯Ό μ§€μ •λœ λ‹¨μœ„λ‘œ 자λ₯΄κΈ° | 'date_trunc' ν•¨μˆ˜ μ„€λͺ…
μƒλ‹¨μœΌλ‘œ

ν‹°μŠ€ν† λ¦¬νˆ΄λ°”