time stamps in ABAP

[Michael_Keller]

Dear community, I recently noticed the LASTCHANGEDATETIME field in the EKKO database table in a S/4HANA system. At the moment I'm not sure whether this field only exists in a S/4HANA because I didn't find it in a SAP ECC EHP8.

However, if the field stores exactly what the name implies, that would be great. The date and time a document was created or changed has always been an important information for me. In addition, my interest in this topic was piqued - please follow my little journey... 🙂

First steps

Ok, the starting point was the data element of the field. That's CHANGEDATETIME (domain TZNTSTMPL). As the documentation of the data element says: The field stores a time stamp.

Here is some help about time stamps in ABAP and a little example I've tried out. Please pay attention to the short form (data element TIMESTAMP) and long form (data element TIMESTAMPL) as described in the online help.

REPORT ztime_stamps.



DATA lv_short_time_stamp TYPE timestamp.

DATA lv_long_time_stamp TYPE timestampl.



GET TIME STAMP FIELD lv_short_time_stamp.

GET TIME STAMP FIELD lv_long_time_stamp.



DATA(lo_output) = cl_demo_output=>new( ).



lo_output->write( lv_short_time_stamp ).

lo_output->write( |{ lv_short_time_stamp timestamp = ISO timezone = sy-zonlo }| ).



lo_output->write( lv_long_time_stamp ).

lo_output->write( |{ lv_long_time_stamp timestamp = ISO timezone = sy-zonlo }| ).



lo_output->display( ).

The following screenshot shows the result.

example result

During my further search on the Internet for more information, I found this page. Very interesting. It contains several examples of how to deal with time stamps in ABAP. Especially calculating (see class CL_ABAP_TSTMP).

The story went on

The where-used list for the data element TIMESTAMPL (see example above) allowed me to find various database tables in which time stamps in long forms are used. Among other things, the TDEVC was listed. A look at this database table via transaction SE16 showed that not all entries have a time stamp. My Z-namespace development packages, for example, don't have one - for whatever reason?

The BW_SDATA package has a time stamp: 20.130.925.094.535,3021930 in the field TECH_CHG_TSTMP.

Formatting in Excel

My next question was about Excel and time stamps. Transaction SE16 is often used to export selected data records to Excel. If these data records contain time stamps, then one would certainly want to format them for better reading. Yep, even for a computer scientist, 20.130.925.094.535,3021930 is not nice to read 🙂

To my surprise, that was exported directly to match. No further formatting necessary. Great!

ready to read after export

Should there ever be a requirement to convert the time stamp in Excel, this can work as described below. The function "=TEXT(A1;"0000-00-00 00\:00\:00")+0" and some user defined cell format a la "TT/MM/JJJJ hh:mm:ss" (German Excel version) solved the problem when cell A1 contains the time stamp. Here's an example.

time stamp from A1 formatted in B1

Final question

The question remains whether the times of ERDAT/AEDAT and ERZET/AEZET fields are over now? Or long gone and I didn't notice? I know of many newer custom database tables that contain such fields and which are well suited for their purpose.

Should we now only use one time stamp for the creation date and time and another time stamp for the change date and time? And what does that mean for ABAP program logic and ABAP CDS (I've found this help page)?

What are your experiences? Please share. I'm curious. Thanks in advance.

 

Best regards, thanks fo reading and stay healthy

Michael

[mornee_parsons]

My biggest issue with the standard date time concept with SAP is when developing SAPUI5 applications. I always have to enhance standard structures with extra timestamp fields to get it working properly. I really wish SAP can get rid of the normal 2 date / time fields

[hardyp180]

My understanding is that when you create tables to be used in the RESTful ABAP Programming model you are supposed to use those new time stamp fields for CREATEDAT and CHANGEDAT

In fact I think it is vital to use them if you want the locking to work properly and if you want to generate everything automatically based on one or more tables using the RAP generator.

So I would say yes - ERDAT and ERZET are going to be consigned to the dustbin of history.

[matt]

Not exactly related to your blog, but fun fact. Depending on the underlying OS, the timestamp produced by GET TIMESTAMP may not use all available decimal places.

[Michael_Keller]

Do I then have to program depending on the operating system? 😉

[matt]

You might. It depends on what you're doing. The documentation says:

The precision of the decimal places of the long form depends on the hardware (processor) of the application server. The maximum resolution of 100 ns is not always reached. On some platforms, only a resolution of milliseconds can be reached.

I found this the hard way. I was generating unique records - but sometimes they weren't! Of course nowadays I'll use a GUID.

[mmcisme1]

FYI - from CDS as it took me a bit to find it....  I needed two fields.

tstmp_to_dats( _obj.crea_tmp,
abap_system_timezone( $session.client,'NULL' ),
$session.client,
'NULL' ) as wfstartdat,

tstmp_to_tims( _obj.crea_tmp,
abap_system_timezone( $session.client,'NULL' ),
$session.client,
'NULL' ) as wftime,

[Sandra_Rossi]

Link: here (and also here).

[Michael_Keller]

As always: Thanks for your valuable experience! 🙂

[lbreddemann]

Time-related data really is a gift that keeps on giving...

From the HANA point of view, I've already written a few posts (Trouble with time , The time is now, isn't it?, Be careful with dates and timestamps) but the topic comes up again and again.

With your ABAP-perspective, we could nearly go on and write an "SAP and time" book. Just imagine, how may funny sub-titles we can come up with for something like that 😄

 

 

[Michael_Keller]

Your blogs are really great!

The idea with the book is very interesting. Time plays a very important role in IT and therefore in SAP ERP systems. I remember when I changed the system time of a SuSE Linux Enterprise Server back in 2004. The SAPDB database crashed. It couldn't cope with the leap in time into the future. What fun...

[lbreddemann]

Good old SAPDB - must have been around version 7.3 around that time. Despite all its shortcomings it holds a special place for me as it was the first DBMS where I saw and learned “how the sausage was made” after just using Oracle for years before that.

And yes, it sure wasn’t the only transaction system that assumed time progressing one step at a time towards future.

[wouter_peeters]

Interesting, can we conclude that to be flexible and future proof, we always need to add 3 fields: Date, Time and DateTimestamp?

If the logic needs to be used from SAP Backend, by a transaction with select options/selection criteria on date and time, it is performance wise best to use and query on the seperate date and time fields?

If the logic needs to be used from SAPUI5/RAP, best to use the timestamp?

[Michael_Keller]

Very interesting thoughts, Wouter! Thanks for the comment. I guess even ALV IDA has no direct solution for this. So if you need a classic reporting in your SAP ERP backend via ALV IDA (based on ABAP CDS), you still need SELECT-OPTIONS. I've never tried a SELECT-OPTION based on a TIMESTAMP/TIMESTAMPL data element. Interesting test for tomorrow. I'll report.

[wouter_peeters]

I was even talking about regular ALV, because I always avoidIDA ALV due to: not possible to run in background, subtotals don't work, no filtering possible on calculated columns, no rightclick/selection, ...

If I remember, in a regular ALV report and report selection, the timestamp is not easy to enter for the enduser, thus providing a date and time seperately, while the database still has a timestamp, requiring mapping en resulting in performance issues.

[Michael_Keller]

Ok, I've made my test. No value help, but some hint how you have to input values for a SELECT-OPTION of type TIMESTAMPL:

"Input must be in the format __.___.___.___.__~,_______"

That looks hard to fullfill by user - or is it a kind of challenge? 😉

 

[shais]

You should use data elements with conversion routine TSTLC (domain COM_TSTMP).

There is no standard search help for timestamps, I'm afraid.

[wouter_peeters]

Oh dear, still not very user friendly.

Perhaps we can rephrase: as a best practice always include: Date, Time and DateTimeStamp fields.

• For classical use, utilize the seperate fields for User selection and Querying the database. Rationale: User Experience, Simplicity, Performance
• For Restful use, the timestamp will be used in regards to locking
  • Any UI5 experts that can elaborate the rationale? I see Paul indicated that timestamp is needed for locking purposes in RAP

[Föß]

Hi Michael,

I just want to add that a new timestamp type is available in 7.54: UTCLONG. With 7.54 and 7.55 also some new sql expressions are available: TSTMPL_TO_UTCL, TSTMPL_FROM_UTCL, UTCL_CURRENT, UTCL_ADD_SECONDS and UTCL_SECONDS_BETWEEN.

https://help.sap.com/doc/abapdocu_754_index_htm/7.54/en-US/abennews-754-types.htm

lg Föß

[Michael_Keller]

Thank you for this really up-to-date notice. Release 7.54 is way ahead 🙂

[Clemenss]

SAP was and is a multi-languange mult-regional syste, from the very beginning.

A time stamp is defined as a combined date/time for identifying a point in time independent of time zone. This helps us to know the sequence of any transactional database changes i.e in SAP system used from anywhere in this world.

An SAP time stamp is always given in Coordinated Universal Time UTC. According to POSIX 1.2001 a time stamp reflects days of 86400 seconds.

The well-known creation and modification date and time fields are always local time and date.

Although SAP provides ABAP commands for conversion of timestamps to and from  local date/time, we must use some unreleased function modules or class methods to do simple calculations like adding or subtracting time spans.

We can easily add or subtract integer numbers to date fields - no such handling for timestamps for very long time.

The new internal datatype utclong together with built-in functions utclong_add and utclong_diff and class cl_abap_utclong will make things much easier.

Thank you for making us think about time stamps.

Regards, Clemens

[Michael_Keller]

Always in service for the SAP Community 😉 Many thanks for sharing your experiences. Good note about the UTC and local date/time difference. That's an important point!

[Astashonok]

wouter.peeters and is there any friendly UI control in SAPUI5 that allows specifying timestamp for filtering?

[avik_ghosh]

Is there a way to find out Timestamp field when checking data type in Dynamic structure determination with RTTS. For Long Timestamp it shows P of Length 11 with Decimal 7 and Normal Timestamp with P of Length 8.

This is with reference to CL_ABAP_STRUCTDESCR->COMPONENTS.

[Sandra_Rossi]

@avik_ghosh The blog post is not about RTTS. RTTS provide all information you need. If the component is a simple type, cast it as CL_ABAP_ELEMDESCR which contains the definition. You have lots of sample code in the Web. If you don't find it, ask a question in technology Q&A forum with ABAP Development tag.