Tuesday, February 9, 2021

Convert numbers to times and then into timestamps

convert number to dates and timestamps in sql

I am sure that most of us work with ERP databases where the date and time fields are really numbers. Finding ways to convert these numbers into "real" dates and times allows us to make use of all the rich date and time features within the IBM i operating system.

I have described in a previous post how to convert various numeric versions of a date to a real date. So I won't bother to repeat that.

But what about converting numbers into a real time?

Before I start giving examples let me show the file that contains the data I will be using. Yes, I am using a file as in my experience if there are numbers pretending to be dates and times then they are in DDS files, rather than DDL Tables. The file contains four fields:

A..........T.Name++++++RLen++TDpB......Functions+++++++++++
A          R TESTFILER
A            STRDATE        7P 0
A            STRTIME        6P 0
A            ENDDATE   R               REFFLD(STRDATE *SRC)
A            ENDTIME   R               REFFLD(STRTIME *SRC)

I don't think I need to explain what these fields contain as their names adequately explain their function.

As this is only an example the file contains two records:

01  SELECT * FROM TESTFILE ;


STRDATE  STRTIME  ENDDATE  ENDTIME
-------  -------  -------  -------
 920630   110000  1210101        0
1201231    45322  1210125   136060

The dates are in CYMD format, and the times are in 24 hours format. Notice that the End Time for the second record is not a valid time.

The first step is to convert the date fields to a "real" date, and the time fields to a "real" time.

01  SELECT STRDATE,
02    DATE(TIMESTAMP_FORMAT(CHAR(19000000 + STRDATE),'YYYYMMDD'))
        AS "Date",
03    STRTIME,
04    TIME('00:00:00') + STRTIME AS "Time",
05  FROM TESTFILE ;

Line 2: This is how to convert a number pretending to be a CYMD date to a "real" date.

Line 4: I add the number pretending to be a time to a real time field of zero. The result will be the "real" time of the number.

The results are:

STRDATE  Date        STRTIME  Time
-------  ----------  -------  --------
 920630  1992-06-30  110000   11.00.00
1201231  2020-12-31   45322   04.53.22

What happens if I do the same with that invalid time in the End time field?

01  SELECT ENDDATE,
02    DATE(TIMESTAMP_FORMAT(CHAR(19000000 + ENDDATE),'YYYYMMDD'))
        AS "Date",
03    ENDTIME,
04    TIME('00:00:00') + ENDTIME AS "Time"
05  FROM TESTFILE ;

The results did surprise me:

STRDATE  Date        STRTIME  Time
-------  ----------  -------  --------
1210101  2021-01-01        0  00.00.00
1210125  2021-01-25   136060  14.01.00

The surprise is the number 136060, which is an invalid time, was converted to 14.01.00!

As I have a "real" dates and times I can make a timestamp from them.

The basic syntax for doing this is:

01  SELECT 
02    TIMESTAMP(CHAR(< date >),CHAR(< time >))
03    FROM < file > ;

Line 2: The TIMESTAMP function combines character representations of the date and time into a timestamp. Notice how they are separated by a comma.

In my example I need to convert the numeric date and time fields into dates and times before I can combine them into a timestamp. My statement would look like:

01  SELECT 
02  TIMESTAMP(
03    CHAR(DATE(TIMESTAMP_FORMAT(CHAR(19000000 + STRDATE),
        'YYYYMMDD'))),
04    CHAR(TIME('00:00:00') + STRTIME)) AS "Start Timestamp",
05  TIMESTAMP(
06    CHAR(DATE(TIMESTAMP_FORMAT(CHAR(19000000 + ENDDATE),
        'YYYYMMDD'))),
07    CHAR(TIME('00:00:00') + ENDTIME)) AS "End Timestamp"
08  FROM TESTFILE ;

Line 2 and 5: The TIMESTAMP function starts here, and ends with the last parentheses ( ) ) on lines 4 and 7.

Line 3 and 6: Convert the number to a date, and then convert the result to a character literal. Notice how this line ends with a comma this is used to separate the date from the time.

Line 4 and 7: This converts the date I created by the method I described before, into a character literal.

The TIMESTAMP has combined the two into a timestamp type column, see below:

Start Timestamp             End Timestamp
--------------------------  --------------------------
1992-06-30 11:00:00.000000  2021-01-01 00:00:00.000000
2020-12-31 04:53:22.000000  2021-01-25 14:01:00.000000

Now I have these timestamps I can start using them for all kinds of things, that I could not when they were separate numbers.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.

5 comments:

  1. Wow! What timing I was just working on a project today that needs this. Thank you!!

    ReplyDelete
  2. I've been using these techniques a lot recently.

    ReplyDelete
  3. Hello, I have a column called duration in numeric format that I need to convert to hour format and then subtract another hour, the problem is that the column has values greater than 24 hours, for example 450000,300000,270000 and when converting them to hour format clearly fails. Is there any way to solve it?

    ReplyDelete
    Replies
    1. Are those values seconds?
      This might be the time to write a UDF to use RPG to convert those seconds to hours.

      Delete
  4. Wow I have used timestamp and Date function a lot. Motivated to post such stuff on the blog as well 🙂

    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.