SAP
storing date and time in database
Hello,
I would like to ask you for recommendations and guidelines on how to store date and time in datatables. We are facing some issues due to different timezones used in different tables. Sometimes date and time is stored in separate columns and we use system time (sy-datum and sy-uzeit). In other tables we are using one column with timestamp. There are also mixes with date, time and timestamp columns.
The problem is that GET TIME STAMP FIELD returns date and time in UTC, while system variables store date and time in system time (in our case it's CET). That causes issues while comparing data and implicates additional conversions. Also summer time causes additional problems.
We would like to align on common clean approach, especially when introducing new tables in our developments. What are the best practices in this area?
What about legacy systems, when date and time is stored in CET? Should we migrate and convert it? Or just leave it as it was designed?
Thank you in advance for all your inputs.
I would also be interested in a guideline. Some things to keep in mind that make using just a single approach for everything mostly impossible:
- Support for timestamps on the ABAP side is quite release dependent. The ABAP SQL and CDS functions to do comparisons of timestamps or conversions / extractions of timestamp components are very new. On 7.4 this is even dependent on the patch level you are using.
- There are multiple different timestamp formats available(, again release dependent?). Some include the timezone, some don't. Some are stored as char with fixed length others as numbers (or even hex encoded ?). On the OData side there are also different formats and conversions taking place that affect the user of the OData service.
- Timestamps currently have no native support in traditional Dynpro based UIs. There are no value helps, ALVs cannot split time and date on their own etc. You cannot even display some of the formats on a dynpro natively.
- Date and time fields have inbuilt validation in dynpro programming.
- Time dependent data in customizing, usually a timespan with valid from and valid to or one or the other, is quite common. Timestamps seem very impractical here, especially since the table maintenance generator again cannot handle all formats (or any actually?).
- If only a daily precision is required then the timezone handling also becomes dangerously difficult. I once had the problem that in business partner (AP-MD-BP) relations are stored with a validity using timestamps. But they only have a precision of the day so the time component of the timestamp was always 00:00:00. The timestamp was in UTC though which lead to edge cases when daylight saving time was activated where relations were interpreted with one day off (...).
I am currently mostly using a date and a time field for storing a timestamp and am using the system time. But I also do mostly dynpro programming for UIs and have no users in different timezones using the same system. Timestamps only got to me when using OData or business partner.
Edit: The topic of UUIDs seems quite similar by the way. Lots of different formats with various levels of support on the different technologies.
IMHO, date/time is good for business-relevant times, such as document dates, creation date etc. Timestamps are more useful for technical things. Sometimes I'll even have both. Among the questions I ask myself: Would someone want to search by date? ==> date/time Do I need more precision than seconds? ==> Timestamp
Things like invoice date or delivery date is generally considered local timezone, i.e. of the company code. It's awkward for a halfway technical business user (and there are plenty of these in my experience) to fire up SE16N and enter 18 digit timestamps and adjust for UTC just to find last month's invoices.
Not sure Clean ABAP is the right place for this. This is more a question of good database modelling, not so much of readability.
Dates and times are never easy to handle, and a complication that's often overlooked by product owners and misunderstood by requirements engineers. Thank goodness we haven't expanded beyond Earth so far, and don't have to think about day/month/year durations on other planets, irregular orbits that produce non-constant durations, and the way time shifts for observers at relativistc speeds... 😁
If you only want to capture the precise date and time when something happened, just record a UTC timestamp. That's ABAP's TIMESTAMP or TIMESTAMPL data types. Even if you don't need the time, it's far easier to waste the few extra bytes than to settle questions like "did it happen on August 26, 2020 because John was in California at the time, and for me in London it would have occurred on August 27, 2020?"
Otherwise, it's up to you to analyze the use case and decide what you need.
Many cases will be answered by "I only need a date, don't overcomplicate". For example, you can make putting a due date on your tasks tricky by asking whether travelling westwards should give you some extra hours to complete your work, or whether it's worth considering that you might call a couple hours too early if you record birthdays without a time zone. But mostly, people will just don't care, and a simple date with ABAP type DATS, the 8-character field, will be sufficient.
Other cases may be simply "I only need a time, don't overcomplicate". In these, a time without date and time zone might be sufficient. For example, when setting the wake up alarm time on your mobile device to "6:00 am", you probably don't expect it to go off at "9:00 am" after you travelled eastwards. For these, the ABAP data type TIMS, the 6 character field, is usually enough.
Still other cases may require asking all the tricky questions. For example, in a stock market setup with global realtime shareholder transactions, it's probably unwise not to capture, date, time, and time zone. Whether you store them together as a timestamp or in separate fields however, may depend on what you do with that data: Are you regularly performing complicated interval calculations that might be easier to implement in SAP HANA with timestamps than with separate fields? Is the storage mainly for storing user input, and you just don't care? Is it a common case that the time entry will be varied afterwards, independently from the date, such that storing it in a separate field might make updating easier?
This is more a question of good database modelling, not so much of readability.
No objections to this, hence closing.
