Date

Timezone

---

1. Timezone: 동일 표준 시간을 채택하고 있는 지역
2. GMT & UTC: 현재 시간은 동일하나 개념이 다름

• GMT : Greenwich Mean Time (timezone O)
• UTC: Coordinated Universal Time (timezone X, standard)

WITH TIME ZONE

-- Timezone = UTC
SELECT  '2021-04-16 00:00:00+03'::timestamp; --timezone('03') is ignored

/*
      timestamp
---------------------
 2021-04-16 00:00:00
*/

SELECT '2021-04-16 00:00:00+03'::timestamp WITH TIME ZONE;

/*
      timestamptz
------------------------
 2021-04-15 21:00:00+00
*/

-- Timezone = Asia/Seoul
SELECT  '2021-04-16 00:00:00+03'::timestamp; --timezone('03') is ignored

/*
      timestamp
---------------------
 2021-04-16 00:00:00
*/

SELECT '2021-04-16 00:00:00+03'::timestamp WITH TIME ZONE;

/*
      timestamptz
------------------------
 2021-04-16 06:00:00+09
*/

Timestamp Datatype

1. timestamp without timezone

• 입력값의 로컬 타임을 타임존 없이 저장함.
• 출력 시 데이터베이스의 타임존에 상관없이 항상 같은 시간으로 표시됨.

1. timestamp with timezone

• 입력값은 UTC 기준 시간으로 변환되어 데이터베이스에 저장됨.
• 출력 시 데이터베이스의 타임존에 맞는 시간으로 변환하여 보여줌.

-- Table
/*
 table_name | column_name |          data_type
------------+-------------+-----------------------------
 times      | ts          | timestamp without time zone
 times      | tz          | timestamp with time zone
*/

-- Timezone = 'UTC'
INSERT INTO times (ts, tz) VALUES (
    '2021-04-16 00:00:00+03'::timestamp,
    '2021-04-16 00:00:00+03'::timestamp
);

INSERT INTO times (ts, tz) VALUES (
    '2021-04-16 00:00:00+03'::timestamp WITH TIME ZONE,
    '2021-04-16 00:00:00+03'::timestamp WITH TIME ZONE
);

SELECT * FROM times;

/*
         ts          |           tz
---------------------+------------------------
 2021-04-16 00:00:00 | 2021-04-16 00:00:00+00
 2021-04-15 21:00:00 | 2021-04-15 21:00:00+00
*/

-- Timezone = Asia/SeoulSET TIMEZONE = 'Asia/Seoul';
SELECT * FROM times;

/*
         ts          |           tz
---------------------+------------------------
 2021-04-16 00:00:00 | 2021-04-16 09:00:00+09
 2021-04-15 21:00:00 | 2021-04-16 06:00:00+09
*/

Current Date

---

SELECT NOW()::date;
SELECT CURRENT_DATE;
SELECT TO_CHAR(CURRENT_DATE, 'dd/mm/yyyy'); -- Format modifiers

    now
------------
 2021-04-16

 current_date
--------------
 2021-04-16

  to_char
------------
 16/04/2021

Functions

---

DATE

-- Date difference in days
SELECT date '1800/01/01'-'1799/01/01';

-- Cast data into date format
SELECT date '1800/01/01';

 difference
------------
    365

    date
------------
 1800-01-01

AGE

SELECT AGE (date '1999/01/01');

           age
-------------------------
 22 years 3 mons 15 days

EXTRACT

SELECT EXTRACT (DAY FROM date '1992/11/13') AS day;
SELECT EXTRACT (MONTH FROM date '1992/11/13') AS month;
SELECT EXTRACT (YEAR FROM date '1992/11/13') AS year;

 day
-----
  13

 month
-------
  11

 year
------
 1992

DATE_TRUNC

SELECT DATE_TRUNC ('year', date '1992/11/13') AS year_trunc;
SELECT DATE_TRUNC ('month', date '1992/11/13') AS month_trunc;
SELECT DATE_TRUNC ('day', date '1992/11/13') AS day_trunc;

       year_trunc
------------------------
 1992-01-01 00:00:00+00

       month_trunc
------------------------
 1992-11-01 00:00:00+00

        day_trunc
------------------------
 1992-11-13 00:00:00+00

INTERVALS

identifiers: years, months, days, hours, minutes, seconds

SELECT NOW() + INTERVAL '1 year 2 months 3 days' AS date;

             date
-------------------------------
 2022-06-19 05:50:31.719013+00