Date & Time

25 Functions

Date and Time functions allow you to handle temporal data—from tracking when a user last logged in to calculating complex project deadlines. In PostgreSQL, these functions are essential for any data analyst or backend developer working with logs, schedules, or historical records.

NOW()

Description

Returns the current date and time (with time zone).

Syntax

NOW()

When to Use

Recording the exact moment a transaction or a "Quest" is completed.

Example Query

SELECT NOW();

💡 Pro Tip

Dalam transaksi yang panjang, NOW() akan selalu mengembalikan waktu yang sama (awal transaksi). Gunakan CLOCK_TIMESTAMP() jika butuh waktu yang benar-benar aktual di setiap baris.

CURRENT_DATE

Description

Returns the current date without the time component.

Syntax

CURRENT_DATE

When to Use

Generating a daily report or filtering orders made "today."

Example Query

SELECT * FROM orders WHERE order_date = CURRENT_DATE;

💡 Pro Tip

Ini adalah cara paling efisien untuk membandingkan tanggal saja tanpa perlu melakukan casting atau pembersihan waktu secara manual.

CURRENT_TIME

Description

Returns the current time (with time zone) without the date.

Syntax

CURRENT_TIME

When to Use

Checking if a store is currently open or tracking specific login hours.

Example Query

SELECT CURRENT_TIME;

💡 Pro Tip

Kamu bisa menentukan presisi detiknya, misalnya CURRENT_TIME(0) untuk membuang milidetik.

LOCALTIMESTAMP

Description

Returns the current date and time without time zone information.

Syntax

LOCALTIMESTAMP

When to Use

Storing time for a local event where time zone offsets aren't required.

Example Query

SELECT LOCALTIMESTAMP;

💡 Pro Tip

Untuk aplikasi global seperti XQLora, disarankan tetap menggunakan TIMESTAMPTZ agar waktu user di berbagai negara tetap akurat.

EXTRACT()

Description

Retrieves sub-fields (like year, month, day) from a date/time value.

Syntax

EXTRACT(field FROM source)

When to Use

Finding which day of the week (DOW) gets the most "Arena" matches.

Example Query

SELECT EXTRACT(HOUR FROM created_at) FROM users;

💡 Pro Tip

Gunakan EXTRACT(DOW FROM date) untuk mendapatkan hari (0 = Sunday). Ini sangat berguna untuk analisis perilaku mingguan user.

DATE_PART()

Description

A legacy/alternative way to extract sub-fields, returning a double precision value.

Syntax

DATE_PART('field', source)

When to Use

Similar to EXTRACT, often used for mathematical operations on dates.

Example Query

SELECT DATE_PART('year', NOW());

💡 Pro Tip

EXTRACT lebih standar SQL, sementara DATE_PART lebih spesifik ke gaya PostgreSQL/Informix. Keduanya bekerja sangat mirip di Postgres.

DATE_TRUNC()

Description

Truncates a timestamp to a specified level of precision (e.g., to the nearest month).

Syntax

DATE_TRUNC('precision', source)

When to Use

Aggregating total XP earned by month.

Example Query

SELECT DATE_TRUNC('month', created_at), SUM(xp)
FROM achievements
GROUP BY 1;

💡 Pro Tip

Ini adalah "senjata rahasia" untuk membuat time-series reports. Semua waktu di bulan yang sama akan dipaksa menjadi tanggal 1 jam 00:00:00.

AGE()

Description

Calculates the difference between two timestamps and returns it as an interval (Years, Months, Days).

Syntax

AGE(timestamp1, timestamp2)

When to Use

Calculating how long a user has been a member of XQLora.

Example Query

SELECT AGE(NOW(), joined_at) FROM users;

💡 Pro Tip

Jika hanya satu argumen yang diberikan (misal AGE(timestamp)), fungsi ini otomatis membandingkannya dengan tanggal hari ini.

TO_CHAR()

Description

Converts a timestamp to a string using a custom format.

Syntax

TO_CHAR(source, 'format')

When to Use

Displaying "Joined on Monday, 20 Jan 2026" on the user profile.

Example Query

SELECT TO_CHAR(NOW(), 'Day, DD Mon YYYY');

💡 Pro Tip

Format 'FMDay' (Fill Mode) sangat berguna untuk menghapus spasi kosong yang biasanya muncul di akhir nama hari.

10.

TO_DATE()

Description

Converts a string to a date value.

Syntax

TO_DATE(string, 'format')

When to Use

Parsing user-provided text inputs into actual database dates.

Example Query

SELECT TO_DATE('2026/02/11', 'YYYY/MM/DD');

💡 Pro Tip

Pastikan format string cocok persis dengan pola yang kamu berikan agar tidak terjadi error konversi.

11.

TO_TIMESTAMP()

Description

Converts a string or a Unix epoch number to a timestamp.

Syntax

TO_TIMESTAMP(string/number, 'format')

When to Use

Converting a timestamp from an external API (often in Unix seconds) to a Postgres timestamp.

Example Query

SELECT TO_TIMESTAMP(1739274000);

💡 Pro Tip

Sangat krusial saat melakukan migrasi data atau integrasi dengan data JavaScript/Node.js yang sering menggunakan format epoch.

12.

MAKE_DATE()

Description

Creates a date from Year, Month, Day integers.

Syntax

MAKE_DATE(year, month, day)

When to Use

Generating dates programmatically.

Example Query

SELECT MAKE_DATE(2026, 2, 11);

💡 Pro Tip

Berguna saat data tanggal tersimpan di kolom terpisah (year, month, day) dan perlu digabungkan.

13.

MAKE_TIME()

Description

Creates a time from Hour, Min, Sec.

Syntax

MAKE_TIME(hour, min, sec)

When to Use

Setting specific event hours.

Example Query

SELECT MAKE_TIME(14, 30, 0);

💡 Pro Tip

Parameter detik menerima double precision, jadi kamu bisa membuat waktu dengan presisi milidetik.

14.

MAKE_TIMESTAMP()

Description

Creates a full timestamp from components.

Syntax

MAKE_TIMESTAMP(year, month, day, hour, min, sec)

When to Use

Combining separate date/time columns.

Example Query

SELECT MAKE_TIMESTAMP(2026, 2, 11, 14, 30, 0);

💡 Pro Tip

Gunakan MAKE_TIMESTAMPTZ jika kamu membutuhkan timestamp yang sadar timezone.

15.

INTERVAL

Description

Adds/subtracts time (e.g., + interval '1 day') for date arithmetic.

Syntax

timestamp + INTERVAL 'value unit'

When to Use

Calculating expiration dates for 7-day trials.

Example Query

-- Add 7 days to current date
SELECT NOW() + INTERVAL '7 days';
-- Subtract 1 month
SELECT NOW() - INTERVAL '1 month';

💡 Pro Tip

INTERVAL bisa dikombinasikan: INTERVAL '1 year 2 months 3 days'. Sangat fleksibel untuk kalkulasi durasi kompleks.

16.

AT TIME ZONE

Description

Converts a timestamp to a different time zone.

Syntax

timestamp AT TIME ZONE 'zone'

When to Use

Displaying local time for global users.

Example Query

SELECT NOW() AT TIME ZONE 'Asia/Jakarta';

💡 Pro Tip

Untuk user Indonesia (WIB), gunakan 'Asia/Jakarta'. Untuk WITA gunakan 'Asia/Makassar', dan WIT gunakan 'Asia/Jayapura'.

17.

LOCALTIME

Description

Same as CURRENT_TIME but without timezone.

Syntax

LOCALTIME

When to Use

Local logging without offsets.

Example Query

SELECT LOCALTIME;

💡 Pro Tip

Cocok untuk aplikasi yang hanya berjalan di satu timezone dan tidak perlu konversi.

18.

ISFINITE()

Description

Checks if a date is not 'infinity' or '-infinity'.

Syntax

ISFINITE(date/timestamp/interval)

When to Use

Validating historical or future-bound data.

Example Query

SELECT ISFINITE(DATE '2026-02-11'); -- true
SELECT ISFINITE(DATE 'infinity');   -- false

💡 Pro Tip

PostgreSQL mendukung nilai 'infinity' dan '-infinity' untuk tanggal. Fungsi ini membantu memfilter data yang valid.

19.

DATE_BIN()

Description

"Bins" timestamps into fixed intervals (e.g., 5-min buckets).

Syntax

DATE_BIN(stride, source, origin)

When to Use

Advanced analytics for high-frequency logs.

Example Query

SELECT DATE_BIN('5 minutes', created_at, TIMESTAMP '2026-01-01')
FROM events;

💡 Pro Tip

Lebih fleksibel dari DATE_TRUNC karena bisa membuat bucket dengan interval non-standar seperti 15 menit atau 2 jam.

20.

CLOCK_TIMESTAMP()

Description

Current time that advances during a transaction.

Syntax

CLOCK_TIMESTAMP()

When to Use

Accurate measurement of long-running queries.

Example Query

SELECT CLOCK_TIMESTAMP();

💡 Pro Tip

Berbeda dengan NOW() yang "beku" selama transaksi, CLOCK_TIMESTAMP() selalu mengembalikan waktu terkini.

21.

STATEMENT_TIMESTAMP()

Description

Time at which the current statement started.

Syntax

STATEMENT_TIMESTAMP()

When to Use

Auditing query execution start times.

Example Query

SELECT STATEMENT_TIMESTAMP();

💡 Pro Tip

Berguna untuk profiling: bandingkan STATEMENT_TIMESTAMP() di awal dan CLOCK_TIMESTAMP() di akhir query untuk mengukur durasi.

22.

TRANSACTION_TIMESTAMP()

Description

Synonymous with NOW() — returns transaction start time.

Syntax

TRANSACTION_TIMESTAMP()

When to Use

Standard transaction-safe timing.

Example Query

SELECT TRANSACTION_TIMESTAMP();

💡 Pro Tip

Secara teknis identik dengan NOW(). Nama ini lebih eksplisit tentang perilakunya.

23.

JUSTIFY_DAYS()

Description

Adjusts interval so 30 days = 1 month.

Syntax

JUSTIFY_DAYS(interval)

When to Use

Cleaning up messy interval results.

Example Query

SELECT JUSTIFY_DAYS(INTERVAL '45 days');
-- Result: 1 mon 15 days

💡 Pro Tip

Mengubah interval "mentah" menjadi format yang lebih bisa dibaca manusia.

24.

JUSTIFY_HOURS()

Description

Adjusts interval so 24 hours = 1 day.

Syntax

JUSTIFY_HOURS(interval)

When to Use

Normalizing duration data for reports.

Example Query

SELECT JUSTIFY_HOURS(INTERVAL '50 hours');
-- Result: 2 days 02:00:00

💡 Pro Tip

Sempurna untuk dashboard yang menampilkan durasi aktivitas user dalam format hari + jam.

25.

JUSTIFY_INTERVAL()

Description

Combination of justify_days and justify_hours.

Syntax

JUSTIFY_INTERVAL(interval)

When to Use

Getting the cleanest "Human Readable" duration.

Example Query

SELECT JUSTIFY_INTERVAL(INTERVAL '1 month -1 hour');
-- Result: 29 days 23:00:00

💡 Pro Tip

Fungsi "all-in-one" yang menggabungkan JUSTIFY_DAYS dan JUSTIFY_HOURS untuk menghasilkan interval paling rapi.

XQLora

XQLora

Date & Time

Table of Contents

NOW()

CURRENT_DATE

CURRENT_TIME

LOCALTIMESTAMP

EXTRACT()

DATE_PART()

DATE_TRUNC()

AGE()

TO_CHAR()

TO_DATE()

TO_TIMESTAMP()

MAKE_DATE()

MAKE_TIME()

MAKE_TIMESTAMP()

INTERVAL

AT TIME ZONE

LOCALTIME

ISFINITE()

DATE_BIN()

CLOCK_TIMESTAMP()

STATEMENT_TIMESTAMP()

TRANSACTION_TIMESTAMP()

JUSTIFY_DAYS()

JUSTIFY_HOURS()

JUSTIFY_INTERVAL()