Manage Time In PostgreSQL: Local vs Server Timezone Issues

Photo by Icons8 Team on Unsplash

Perbedaan zona waktu sering kali terjadi ketika kita handle data dengan consider at time. Dicoba di local pakai client database, aman — aman aja. Tapi waktu up ke server, boom! ada aja yang bikin datanya missmatch.

Kejadian ini udah kurang lebih 2–3x aku alami. Biasanya agak lama solved nya karena lupa dengan behaviour perbedaan time zone di local client database & server database.

Jadi kalau PM nanya, “katanya udah di test di local & range date nya sesuai. Tapi di server kok masih salah ya range nya?” Nah itu jangan asal jawab. Sini aku bisikin cara cek nya, biar kalian bisa dapet cara jawab yang tepat. let’s go!

Gimana cara cek time zone yang sedang aktif?

SSH dulu ke server database, dan run command timedatectl :

doc penulis

• timedatectl : a command-line utility in Linux systems that controls the system’s time and date settings

By default, postgreSQL akan pakai time zone yg sama dengan time yang ada di system. which is UTC here.
Cara cek di local juga sama, kalau keterangan Time zone nya GMT +7, database client kayak dbeaver akan ikut time zone tersebut (even yang kalian buka di dbeaver itu database yang ada di server, time zone nya bakal tetep GMT+7 karena default dbeaver ikutin time zone local kita).

Perhatikan query berikut :

Value ‘1730263147460’ bisa diubah dengan nama kolom epoch date yang kita miliki.

Disini aku coba query melalui Dbeaver. Jika epoch date hanya di convert to_timestamp() saja, maka hasilnya time tersebut akan sama dengan local time di laptopku, which is GMT+7.
Karena sebelumnya timezone di serverku adalah UTC, maka untuk tau value asli dari time yang ada di server → perlu convert dulu ke UTC (value di kolom ‘utc_time’).
value di kolom ‘jakarta_time’ sama persis dengan di localku.

Jadi jika ingin cek data dengan reflect ke Database di server, kita perlu sediakan 2 kolom untuk local time & UTC time. Hasil query dikatakan akurat jika sudah sesuai ketika di cek menggunakan UTC time.

Kalo gitu tinggal ubah time zone sistem jadi GMT+7 aja beres kan?

Tidak semudah itu guys, time zone di server itu udah paling bener pakai UTC aja. Jangan sembarangan ubah. Karena efeknya bisa ke mana — mana dan impact keseluruhan sistem.

Case #1 Kolom Dengan Tipe Data Int (Epoch Date)

Disini kita fokus dulu, problem kita ada di database. Jadi kita cuma perlu setting di sisi database nya aja. PostgreSQL punya mekanisme sendiri untuk menangani data dengan time zone, yaitu pakai AT TIME ZONE.

Pada contoh query dibawah ini, data pada kolom created_at bertipe data integer dengan value date epoch unix timestamp milisecond (contoh: 1760536697000).

SELECT TO_TIMESTAMP(created_at / 1000) AT TIME ZONE 'UTC' AS created_at_utc
from news;

• TO_TIMESTAMP() : Convert string / int value ke timestamp with time zone data type.
• created_at/1000 : Data dibagi 1000 untuk menjadikan epoch milisecond → second.
• UTC : bisa diubah sesuai timezone yang diperlukan. Disini pakai ‘UTC’ karena tidak diperlukan convert ke time zone lain. Detail list time zone selengkapnya bisa dilihat di Time Zone Name For Setting timezone postgreSQL.

Notes:
Jika kebutuhan datanya untuk filter date di time zone Indonesia (GMT+7, maka lakukan convert data di kedua sisi: convert kolom ke time zone yang diperlukan & convert juga data dari input ke time zone yang diperlukan untuk mendapatkan hasil yang sesuai)

Get Deby Silvia A’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Contoh :

SELECT 
  to_timestamp(created_at / 1000) AT TIME ZONE 'Asia/Jakarta' AS created_at_jkt
FROM news
WHERE 
  (to_timestamp(created_at / 1000) AT TIME ZONE 'Asia/Jakarta') >= 
  (to_timestamp(1760345137000 / 1000) AT TIME ZONE 'Asia/Jakarta');

Jika kedua sisi di convert seperti diatas, akan didapatkan hasil yang akurat dalam pengecekan tanggal beserta waktu.

Case #2 Kolom Dengan Tipe Data Timestamp

Timestamp (TIMESTAMP WITHOUT TIME ZONE) merupakan tipe data yang menyimpan date dan time tanpa ada informasi terkait time zone. Biasanya data date time di postgreSQL akan otomatis tersimpan sebagai UTC. Namun karena timestamp (tidak ada info time zone), diperlukan convert ke UTC dulu sebelum convert ke time zone lain. Hal ini untuk menjelaskan bahwa data dari database tersebut memang UTC (postgreSQL tidak tahu kalau data itu UTC).

Tipe data ini cukup sulit diolah jika app kita memerlukan informasi yang akurat berdasarkan perbedaan zona waktu (misalkan app digunakan di banyak negara). Dulu aku pernah mengolah tipe data ini di database legacy yang datanya udah terlanjur banyak, jadi cukup berisiko jika mengubah semua data ke kolom baru timestamptz. Tapi ternyata postgreSQL memiliki solusi atas problem itu.

SELECT ("created_at" AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Jakarta')::timestamptz AS date_jkt;

• AT TIME ZONE 'UTC' : created_at di deklarasikan sebagai time zone UTC.
  output type timestamptz.
• AT TIME ZONE 'Asia/Jakarta': convert UTC timestamp ke Asia/Jakarta local time.
  Setelah AT TIME ZONE yang kedua ini, output typetimestamp without time zone.
• ::timestamptz: cast local timestamp kembali ketimestamptzs

Sekarang, value sudah merepresentasikan Jakarta local time, dengan timezone awareness.

Notes

1. Tipe data int dengan value epoch date lebih minim risiko dan mudah untuk diolah daripada menyimpan data timestamp langsung ke database.
2. Jangan lupa untuk mengubah epoch milisecond ke second dulu dengan divide by 1000 sebelum menggunakan to_timestamp() . Karena to_timestamp() expects seconds, bukan milliseconds.
3. Jika data tidak perlu untuk ditampilkan spesifik di 1 negara, tidak perlu menggunakan timestamptz.
4. Selalu convert kolom asal data maupun data yang dijadikan parameter dalam query untuk hasil yang lebih akurat.
5. Lebih baik convert data ketika akan ditampilkan. Biarkan data di database apa adanya, tetap dalam format UTC.

Tambahan informasi:

• Jika ingin mengecek value dari epoch, bisa cek value nya di website https://www.epochconverter.com/.
  Tapi jika hanya ingin melihat value di local time, bisa juga gunakan inspect element di browser chrome / firefox (actually aku udah coba di 2 browser itu). Ketik “New Date(epoch_value)”.

cek value epoch dari browser

• to_timestamp() memiliki peran penting dalam pengolahan data berupa date time. Bisa digunakan untuk convert data int maupun varchar/string . Selengkapnya di to_timestamp() string

Resources:

9.8. Data Type Formatting Functions

How to pass format to to_timestamp in postgresql?