SQLANDPLSQL.COM

PosgreSQL formatting functions converts various data type (integer, numeric, floating point, date time) to formatted strings and for converting from formatted strings to specific data types

List of Formatting functions in PostgreSQL

1. To_Char
2. To_Date
3. To_Number
4. To_Timestamp

TO_CHAR

Basically there are two type of TO_CHAR functions, one used with char and one with date

Examples :-

Select to_char(2014,’9999′) -> 2014, i.e to_char convert integer 2014 to string 2014

Select to_char(now(),’DD-MM-YYYY’) -> 06-07-2022 i.e converts timestamp into string

postgres=# Select now(),to_char(now(),'DD-MM-YYYY');
               now                |  to_char   
----------------------------------+------------
 2022-07-06 15:25:34.220067+05:30 | 06-07-2022
(1 row)

AGE

AGE is one of the useful date/time function supplied in PostgreSQL. AGE between two date gives you the difference in Years, Months and Days

postgres=# select AGE(current_date,'12-Jan-2020');
          age           
------------------------
 2 years 5 mons 24 days
(1 row)

AGE can be used with date and timestamp arguments

age(timestamp ‘2001-04-10’, timestamp ‘1957-06-13’) → 43 years 9 mons 27 days

age ( timestamp, timestamp ) → interval

age ( date, date ) → interval

age ( timestamp ) → interval

age(timestamp ‘1957-06-13’) → 62 years 6 mons 10 days

EXTRACT

EXTRACT is another useful function, retrieves subfields such as year or hour from date/time values

Few examples given in the below tables

year	extract(year from timestamp ‘2022-07-05 19:09:15’) -> 2022
month	extract(month from timestamp ‘2022-07-05 19:09:15’) -> 7
day	extract(day from timestamp ‘2022-07-05 19:09:15’) ->5
hour	extract(hour from timestamp ‘2022-07-05 19:09:15’) -> 19
minute	extract(minute from timestamp ‘2022-07-05 19:09:15’) -> 9
seconds	extract(second from timestamp ‘2022-07-05 19:09:15.123456’) -> 15.123456
milliseconds	extract(year from timestamp ‘2022-07-05 19:10:15’) -> 15123.456
century	extract(century from now()) -> 21
dow	day of week, extract(dow from now()) -> 2
doy	day of year, extract(doy from now()) -> 186
epoch	extract(epoch from now()) -> 1657029477.791060
julian	extract(julian from now()) -> 2459766.81384544296296296296
microseconds	extract(microseconds from timestamp ‘2022-07-05 19:09:15.123456’) -> 15.123456
quarter	extract(quarter from now()) -> 3
timezone	extract(timezone from now()) -> 19800 ( i.e. 5:30 in seconds )
timezone_hour	extract(timezone_hour from now()) -> 5
timezone_minute	extract(timezone_minute from now()) -> 30
week	extract(week from now()) -> 27

DATE_PART

The date_part function is modeled on the traditional Ingres equivalent to the SQL-standard function extract. Date_Part and Extract functions more over same.

date_part(‘field’, source)

SELECT date_part(‘day’, TIMESTAMP ‘2001-02-16 20:38:40’) -> 16

SELECT date_part(‘day’, now()) -> 5 ( i.e today’s day)

DATE_TRUNC

The function date_trunc is conceptually similar to the trunc function for numbers.

date_trunc(field, source [, time_zone ])

Valid values for field are:
microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

SELECT date_trunc(‘day’, TIMESTAMP ‘2022-06-05 19:55:20’) -> 2022-06-05 00:00:00

SELECT date_trunc(‘year’, TIMESTAMP ‘2022-06-05 19:55:20’) -> 2022-01-01 00:00:00

DATE_BIN

The function date_bin “bins” the input timestamp into the specified interval (the stride) aligned with a specified origin

date_bin(stride, source, origin)

SELECT date_bin(’15 minutes’, TIMESTAMP ‘2020-02-11 15:44:17’,TIMESTAMP ‘2001-01-01’)

-> 2020-02-11 15:30:00

SELECT date_bin(’15 minutes’, TIMESTAMP ‘2020-02-11 15:44:17’,TIMESTAMP ‘2001-01-01 00:02:30’)

-> 2020-02-11 15:32:30

Current Date/Time

PostgreSQL provides a number of functions that return values related to the current date and time.

These SQL-standard functions all return values based on the start time of the current transaction:

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURRENT_TIME(precision)

CURRENT_TIMESTAMP(precision)

LOCALTIME

LOCALTIMESTAMP

LOCALTIME(precision)

LOCALTIMESTAMP(precision)

CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone.

CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIMESTAMP can optionally take a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.

CURRENT_TIME	select CURRENT_TIME -> 20:13:37.337674+05:30
CURRENT_TIMESTAMP	select CURRENT_TIMESTAMP -> 2022-07-05 20:13:37.337674+05:30
LOCALTIME	select LOCALTIME -> 20:16:00.1013
LOCALTIMESTAMP	select LOCALTIMESTAMP -> 2022-07-05 20:16:55.83616
NOW()	select now() -> 2022-07-05 20:17:35.395143+05:30
CURRENT_DATE	select CURRENT_DATE -> 2022-07-05
CLOCK_TIMESTAMP()	select CLOCK_TIMESTAMP() -> 2022-07-05 20:21:00.971717+05:30
TRANSACTION_TIMESTAMP()	select TRANSACTION_TIMESTAMP() -> 2022-07-05 20:21:00.971717+05:30
STATEMENT_TIMESTAMP()	select STATEMENT_TIMESTAMP() -> 2022-07-05 20:21:00.986545+05:30
TIMEOFDAY()	select TIMEOFDAY() -> Tue Jul 05 20:22:32.235574 2022 IST

PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called.

transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns

statement_timestamp() returns the start time of the current statement

(more specifically, the time of receipt of the latest command message from the client). statement_timestamp() and transaction_timestamp() return the same value during the first command of a transaction, but might differ during subsequent commands

clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.
timeofday() is a historical PostgreSQL function. Like clock_timestamp(), it returns the actual
current time, but as a formatted text string rather than a timestamp with time zone
value.

now() is a traditional PostgreSQL equivalent to transaction_timestamp()