IBM i Global

IBM i Global

Connect, learn, share, and engage with IBM Power.

 View Only

  • 1.  Discrepancy Between Documentation and Internal Date Representation in DB2 for i 7.3

    Posted Tue August 06, 2024 10:59 AM

    I am trying to better understand how DB2 for i version 7.3 internally handles dates.  
    In the SQL Reference documentation, I found the following passage:

    The internal representation of a date is a string of 4 bytes that contains an integer. The integer (called the Scaliger number) represents the date.

    However, when I run a query to convert a date to an integer, I get a result that appears to be the concatenation of the year, month, and day in the format YYYYMMDD. For example:

    CREATE TABLE test_date (col_date DATE);
    INSERT INTO test_date (col_date) VALUES ('2024-08-06');
    SELECT INTEGER(col_date) AS date_as_integer FROM test_date;

    The result of the query (date_as_integer field) is 20240806, which does not seem to correspond to the concept of a "Scaliger number".

    Questions:

    Could you clarify how DB2 for i version 7.3 internally represents dates?
    What is the correct way to interpret the results obtained through INTEGER(col_date)?

    Thanks in advance.



    ------------------------------
    Agostino
    ------------------------------


  • 2.  RE: Discrepancy Between Documentation and Internal Date Representation in DB2 for i 7.3

    Posted Tue August 06, 2024 12:48 PM

    IBM has pretty much changed all the utilities to make dates appear as more than the four characters that they really only take. DSPPFM will show more.  Heck, even DMPOBJ shows more.  Using your example:

    00000100 1E000000 014B0000 00000000   0000003C 08000000 00150114 10F1F2F4  *         .                   124*
    F0F8F0F6 F1F2F2F5 F3F50000 40404040   40404040 40404040 40404040 40404040  0806122535                      

    But the date part of that is really only 4 bytes.  You can see that they used the century indicator, 1.  Followed by 24.

    This matches: "The internal representation of a date is a string of 4 bytes. Each byte consists of two packed decimal digits. The first 2 bytes represent the year, the third byte the month, and the last byte the day. "

    This manipulation of all the utilities, and even how DB2 returns the stored data to programs, allows stuff like ancient RPG/36 to see the data in a date column.



    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------

    Original Message


  • 3.  RE: Discrepancy Between Documentation and Internal Date Representation in DB2 for i 7.3

    Posted Tue August 06, 2024 11:06 PM
    Edited by Satid S Tue August 06, 2024 11:34 PM

    Dear Agostino

    There is no discrepancy here. It's a case of the description that does not provide more details that enable understanding. 

    If you Google the word "Scaliger number", you will see the explanation of a Julian period starts on 1 January 4713 BC (Julian calendar) and lasts for 7980 years.   So, it's just a number of days counted from that fixed date in a distant past up to the day the date data value is created. This is therefore used as a reference framework for implementing date data type in DB2. 

    The word "internal" is meant to convey a meaning that it is implemented under the cover and is generally not meant for user to directly access it for any meaningful way. (There is no reason to because of the explanation above.)  It is meaningful to DB2 designers for providing a reference point in creating functions for specifying and manipulating dates for you to use. For example, this is why you can manipulate a DATE column data with + or - sign.   This Scaliger number is not specific to IBM i 7.3. It has been used for a long time now - up to the current release 7.5 - and likely will still be used into the future.

    >>>> What is the correct way to interpret the results obtained through INTEGER(col_date)? <<<<

    You are casting (changing the data type of) the data returned from the column data.  



    ------------------------------
    Satid S
    ------------------------------

    Original Message


  • 4.  RE: Discrepancy Between Documentation and Internal Date Representation in DB2 for i 7.3

    Posted Wed August 07, 2024 01:07 AM

    If in fact the database uses the Scalinger calendar to internally represent the dates, read here for more information:

    -> Oxford Dictionary of Astronomy 

    According to this, the date is represented as an Integer starting from January 1 of the year 4713 bc.

    if that Integer is 4 bytes long (32 bit) and signed - the system can reference about plus/minus 5 million years from the reference date - because it uses days, and not seconds (like the Unix Epoch) - this might give us enough time before we get into trouble. 

    HTH

    Daniel


    Original Message


  • 5.  RE: Discrepancy Between Documentation and Internal Date Representation in DB2 for i 7.3

    Posted Wed August 07, 2024 01:42 AM

    Since nobody can interpret the scaliger number correctly, in all tools (such as STRSQL or Run SQL Scripts in ACS) the scaliger no is converted into a readable date based on the date format specified in your connection or job. If you have a look at the same date column in the same file in different jobs/connections with different date formats you will see the date differently (without having changed anything in the table).

    If you really want to see the scaliger no, just use the JULIAN_DAY function:

    Values(JULIAN_DAYS(Current_Date))

    Documentation JULIAN_DAY



    ------------------------------
    Birgitta Hauser
    Database and Software Engineer
    Selfemployed - Modernization-Education-Consulting on IBM i
    Kaufering
    +49 170 5269964
    ------------------------------

    Original Message


Related Content