PostgreSQL to_char (date/time) with practical examples

PosgreSQL to_char is one of the most used formatting function

There are two broad category for to_char in PostgreSQL ,TO_CHAR with text data type and TO_CHAR with date/time data type.

In this post we will examine To_CHAR with date/time data type

TO_CHAR with date/time data type ( date or time or timestamp or interval)

select to_char(Date ’19-Jul-2022′,’dd-mon-yyyyy’); → ‘2022’, i.e to_char convert date 2022 to text 2022

select to_char(Time ’01:30:15 PM’,’HH24:MI:SS AM’); → ’13:30:15 PM’, i.e to_char convert time 13:30:15 PM to text 13:30:15 PM

Below are the allowed patterns for to_char with date/time/timestamp/interval

PatternDescription
HHhour of day (01–12)
HH12hour of day (01–12)
HH24hour of day (01–23)
MIminute (00–59)
SSsecond (00–59)
MSmillisecond (000–999)
USmicrosecond (000–999)
FF1tenth of second (0–9)
FF2100th of second (0–9)
FF3millisecond (000–999)
FF4tenth of a millisecond (0000–9999)
FF5hundredth of a millisecond (00000–99999)
FF6microsecond (000000–999999)
SSSS, SSSSSseconds past midnight (0–86399)
AM, am, PM or pmmeridiem indicator (without periods)
Y,YYYyear with comma
YYYYyear (4 digit )
YYYlast 3 digit of year
YYlast 2 digit of year
Ylast digit of year
IYYYYISO 8601 week-numbering year
BC, bc, AD or adera indicator (without periods)
MONTHMonth in capital
MonthMonth first letter capital letter
MONabbreviated upper case month name
monabbreviated lower case month name
MMmonth number(0-12)
DAYday name in upper case
dayday name in lower case
DYabbreviated upper case day name
DDDday of year (001-366)
Dday of the week, Sunday (1) to Saturday (7)
IDISO 8601 day of the week, Monday (1) to Sunday (7)
Wweek of month(1-5)
WWweek number of year (1–53)
CCcentury (2 digits)
JJulian Date
QQuarter
RMmonth in upper case Roman numerals (I–XII;I=January)
rmmonth in lower case Roman numerals (i–xii;i=January)
TZupper case time-zone abbreviation (only supported in to_char)
tzlower case time-zone abbreviation (only supported in to_char)
TZHtime-zone hours
TZMtime-zone minutes
OFtime-zone offset from UTC (only supported in to_char)

Some examples (note that you can create many different examples with the pattern given in the table above)

QueryResult
select to_char(Date ’19-Jul-2022′,’dd-mon-yyyy’); 2022
select to_char(Date ’19-Jul-2022′,’WW’);29
select to_char(Date ’19-Jul-2022′,’DD-MON-YYYY BC’);19-JUL-2022 AD
select to_char(Date ’19-Jul-2022′,’W’);3
select to_char(Date ’19-Jul-2022′,’IW’);29
select to_char(Date ’19-Jul-2022′,’CC’);21
select to_char(Date ’19-Jul-2022′,’J’);2459780
select to_char(Date ’19-Jul-2022′,’Q’);3
select to_char(Date ’19-Jul-2022′,’RM’);VII
select to_char(Date ’19-Jul-2022′,’TZ’);IST
select to_char(Date ’19-Jul-2022′,’TZH’);+05
select to_char(now(),’DD-MON-YYYY HH24:MI:SS:MS’);19-JUL-2022 11:45:31:330
select to_char(now(),’DD-MON-YYYY HH24:MI:SS:US’);19-JUL-2022 11:49:36:774541
select to_char(now(),’DD-MON-YYYY HH24:MI:SS:FF1′);19-JUL-2022 11:50:22:4
select to_char(now(),’DD-MON-YYYY HH24:MI:SS:FF2′);19-JUL-2022 11:52:18:21
select to_char(now(),’DD-MON-YYYY HH24:MI:SS:FF3′);19-JUL-2022 11:52:35:442
select to_char(now(),’DD-MON-YYYY HH24:MI:SS:FF4′);19-JUL-2022 11:52:54:844
select to_char(timestamp ‘2022-07-19 11:57:09’, ‘YYYY-MM-DD HH24:MI:SS’);2022-07-19 11:57:09
select to_char(interval ’15h 2m 12s’, ‘HH24:MI:SS’);15:02:12

Another example with table select query

postgres=# select empno,dob,to_char(dob,'DD-MON-YYYY') from emp limit 3;
 empno |    dob     |   to_char   
-------+------------+-------------
    10 | 1977-01-16 | 16-JAN-1977
    11 | 1964-06-25 | 25-JUN-1964
    12 | 1982-02-26 | 26-FEB-1982
(3 rows)

TO_CHAR is very handy in converting and formatting the query result. Also extensively used in ETL transformations

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: