Formatting Function in PostgreSQL

Satu hari ada kebutuhan untuk query ke database postgreSQL, dengan where condition waktu tertentu pada kolom yg bertipe timestamp. Simple sih, tapi fungsi konverternya? Kagak hapal gw

Setelah bertanya sama mbah gugel, akhirnya gw hapal (cuma hapal 1 wkwk) tapi itu udah cukup sakti buat query-query ke kolom bertipe timestamp di postgtreSQL. Fungsi tsb bernama to_timestamp().

Definisinya: to_timestamp(text, text), dimana text pertama berisi tanggal dan/atau jam dan text kedua berisi format yg digunakan. Berikut ini contohnya:

to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp('05 Dec 2000 7:00:00 AM', 'DD Mon YYYY HH:MI:SS AM')

Untuk template pattern yg bisa digunakan untuk format tanggal/jam di parameter kedua:

PostgreSQL Data Type Formatting Functions

The PostgreSQL formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types. Table below list them.  These functions all follow a common calling convention: the first argument is the value to be formatted and the second argument is a template that defines the output or input format.

The to_timestamp function can also take a single double precision argument to convert from Unix epoch to timestamp with time zone. (Integer Unix epochs are implicitly cast to double precision.)

How to change PostgreSQL root password

1. Find file pg_hba.conf. its here:
   C:\Program Files\PostgreSQL\9.1\data
2. Open, and modifiy this line:
   #this for IPv4
   host all all 127.0.0.1/32 trust
   #this for IPv6
   host all all ::1/128 trust
3. Restart your PC
4. After that, login to database as postgres. Note that ‘postgres’ is root user. Now you won’t be asked for the password
5. execute this query:
   ALTER USER postgres WITH PASSWORD ‘<your-new-password>’;
   example:
   ALTER USER postgres WITH PASSWORD ‘bayu’;