FFD returns the space necessary for displaying the value.
SysColumns returns the space needed for storing the value.
BTW a date is always stored as running number starting with January, 1 4713 BC (scaliger no).
Date Formats are only used for making the scaliger no readable.
In DDS a date format could be added, just for making the scaliger no readable with old tools such as UPDDTA or WRKF
You may see the scaliger no if you look at the hex value:
Select Hex(YourDate)
From yourDDSOrSQLFile;
A time is stored as 3 Byte binary value. First Byte includes the hours, second byte the minutes and third byte the seconds.
A timestamp is a composition of the Scaliger No and the binary time value and a few bytes for the micro seconds.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization – Education – Consulting on IBM i
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vern Hamberg via MIDRANGE-L
Sent: Saturday, 16 September 2023 11:57
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>
Subject: Re: Comment on the output of the QSYS2/SYSCOLUMNS view
I went into documentation again about date-time stuff. The late Bruce Vining really knew what this was about. He once told me his wife didn't let him go to social events, he'd talk only about calendars.
Anyhow, here's a link with some info -
https://www.ibm.com/docs/en/i/7.5?topic=values-date. Internally, a date is a Scaliger number, the number of days since noon on January 1, 4713 BCE. This is an integer. There's more info here -
https://www.tondering.dk/claus/cal/julperiod.php. The first link mentions the length in the SQLDA as the length of character representation - that depends on the format, it says. That might be closer to what DSPFFD shows.
I created a PF using DDS, 2 date fields, one DATFMT(*YMD), the other DATFMT(*JUL), and a time field, TIMHMS TIMFMT(*HMS) - DSPFFD reports lengths as 8 , 6, and 8 resp., with mention of format. SYSCOLUMNS shows lengths of 4, 4, and 3, and nothing said about format. SYSCOLUMNS is a view over one or other of the QADB* database cross-reference files, maybe the view leaves out the format.
Time is represented as 3 bytes, first if hours, then minutes, then seconds.
I ran the following SELECT to see internal form, thinking the hex value might show me -
select datymd, hex(datymd), datjul, hex(datjul), timhms,
hex(timhms) from vhamberg/testdattim;
The result is here -
DATYMD HEXYMD DATJUL HEXJUL TIMHMS HEXHMS
2023-09-16 00258A2C 2023-09-16 00258A2C 05.51.17 055117
That integer for the hex values for dates is 2,460,204 - that is the Scaliger number for today. That tondering site above shows it as 2,460,203.91, the decimal part is how much of a day has passed since noon yesterday, so the value in the table appears to be rounded up. Or otherwise adjusted.
Now you can invite me to social events - or not! Bruce knew SO much more than I do!
Regards
Vern
On 9/15/2023 6:49 PM, x y wrote:
On V7R5, the output shows a date data type field has a length of 4
(which is the actual size of the binary field on the persistent
storage device) and a timestamp data type column has a length of 10
(also the actual binary size). I haven't checked a time data type yet.
AFAIK this is not consistent with DSPFD/DSPFFD and API output. Is
this variation by design or is it a miss?
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
midrange.com
