Wednesday, May 24, 2023

Using TO_CHAR to format timestamps

I use the SQL scalar function TO_CHAR all of the time to format numbers, inserting thousand separator characters. I can also use it for formatting timestamps.

In the following examples I am going to show how easy this, and it appears to duplicate a lot of the functionality of the EXTRACT scalar function.

In these examples I am going to show what I can do with a timestamp column, TIMESTAMP1, in a SQL DDL table, TESTTABLE.

Let me show you some examples of what I can do.

01  SELECT TIMESTAMP1,
02         TO_CHAR(TIMESTAMP1) AS "TO_CHAR-ed"
03    FROM TESTTABLE

In this first example I have used the TO_CHAR, on line 2, with no parameters. These results, below, show that all that has happened is that the timestamp has been converted to a variable length character value, VARCHAR.

TIMESTAMP1                   TO_CHAR-ed
--------------------------   --------------------------
2023-05-10 19:14:28.404510   2023-05-10-19.14.28.404510

In this next example I am going to perform some "simple" editing.

01  SELECT TIMESTAMP1,
02         TO_CHAR(TIMESTAMP1,'YYYY-MM-DD HH.MI.SS')
03           AS "TO_CHAR-ed"
04    FROM TESTTABLE

Line 2: I want the date and the time, hours, minutes, and seconds, without the microseconds.

The time in the column I created with the TO_CHAR is different from the value in the original column, I mean more than it does not have the microseconds. The by using HH to denote the hour it returns the hour in 12 hour format, i.e. 19 hours has been translated to 7.

TIMESTAMP1                   TO_CHAR-ed
--------------------------   --------------------------
2023-05-10 19:14:28.404510   2023-05-10 07.05.28

IMHO if I am going to give the 12 hour version of the hour then I need to display whether it is AM or PM.

01  SELECT TO_CHAR(TIMESTAMP1,'YYYY-MM-DD HH12.MI.SS AM') AS "12hr",
02         TO_CHAR(TIMESTAMP1,'YYYY-MM-DD HH24.MI.SS AM') AS "24hr"
03    FROM TESTTABLE

Line 1: The code HH12 and HH do the same thing, return the hour in 12 hour format. I have also added AM too. This will return either AM or PM depending upon the time. If I so wanted I could have used A.M. to return A.M. and P.M. instead.

Line 2: I include the AM in this line just to show it is independent of the hour type. In the real world why would I need "PM" to tell me that 19 hours is in the afternoon?

The results are:

12hr                     24hr
----------------------   ----------------------
2023-05-10 07.05.28 PM   2023-05-10 19.05.28 PM

If I wanted to show a timestamp in an interface that would be used by a user I would want to show the date in the month/day/year format used in the USA, and the hours and minutes of the time including part of the day. Again I can retrieve this using TO_CHAR.

01  SELECT TO_CHAR(TIMESTAMP1,'MM/DD/YY HH12:MI AM')
02    FROM TESTTABLE

Line 1: If I wanted the date in another format, such as the European day/month/year format I would use: DD/MM/YY

Personally I prefer using the HH12 to HH as it avoid confusion which type of hour is returned.

The result is as expected:

00001
----------------------
05/10/23 07:05 PM

Let me start showing some of the cool things we can return when using TO_CHAR. How about the name of the day of the week?

01  SELECT TO_CHAR(TIMESTAMP1,'DAY MM/DD/YY HH12:MI AM'),
02         TO_CHAR(TIMESTAMP1,'Day MM/DD/YY HH12:MI AM')
03    FROM TESTTABLE

Line 1: The addition to the TO_CHAR shown previously is the word DAY, this will return the name of the day of the week in upper case.

Line 1: Day returns the name of the day in titlecase.

This returns the following:

00001                         00002
---------------------------   ---------------------------
WEDNESDAY 05/10/23 07:05 PM   Wednesday 05/10/23 07:05 PM

How about I want the date in words?

01  SELECT TO_CHAR(TIMESTAMP1,'Day Month DD, YYYY')
02    FROM TESTTABLE

In this statement Day returns the name of the day in titlecase, and Month returns the name of the month also in titlecase. If I wanted those only in upper case I would have used DAY and MONTH.

00001
---------------------------
Wednesday May 10, 2023

Next example, how about I want the date in what I know as Julian format, year followed by the number of the day in the year.

01  SELECT TO_CHAR(TIMESTAMP1,'YYYY-DDD'),
02    FROM TESTTABLE

Line 1: The letters DDD returns the day of the year.

This gives me:

00001
---------------------------
2023-130

In my final statement I am going to return other interesting parts from the timestamp.

01  SELECT TO_CHAR(TIMESTAMP1,'Dy') AS "Day",
02         TO_CHAR(TIMESTAMP1,'DY') AS "DAY",
03         TO_CHAR(TIMESTAMP1,'D') AS "DoW",
04         TO_CHAR(TIMESTAMP1,'ID') AS "ISO DoW",
05         TO_CHAR(TIMESTAMP1,'IW') AS "ISO WoY",
06         TO_CHAR(TIMESTAMP1,'Q') AS "Qtr"
07    FROM TESTTABLE ;

Line 1: Dy returns the name of the day as Sun – Sat.

Line 2: DY returns the upper case name of the day, SUN – SAT.

Line 3: D returns a number for the day of the week, 1 = Sunday.

Line 4: ID returns the ISO day of the week, 1 = Monday.

Line 5: IW returns the ISO week of the year.

Line 6: Q returns the quarter number.

The results are:

Day  DAY  DoW  ISO DoW  ISO WoY  Qtr
---  ---  ---  -------  -------  ---
Wed  WED    4        3       19    2

I think all of this is fantastic information that can be returned from the timestamp, letting it be so much more than just... a timestamp.

 

This article was written for IBM i 7.5 TR1 and 7.4 TR7.

3 comments:

  1. Excelent post Simon, thanks for sharing

    ReplyDelete
  2. SImon, tu sugerencia de edicion de fecha tiene el siguiente detalle, en vez de mostrar los minutos esta mostrando el mes de la fecha, debiera ser asi el formato TO_CHAR(CURRENT TIMESTAMP , 'YYYY-MM-DD HH24.mi.ss') y el resultado es
    INDIC HORA ACTUAL TO_CHAR
    2023-05-24-13.40.50.852592 2023-05-24 13.40.50

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.