Date and Time in SAP Gateway Foundation
In a first blog post regarding conversions in SAP Gateway Foundation we discussed their relevance in the context of the differences between the ABAP type system and the OData type system. Although not directly related to conversions the handling of date and time perfectly fits into this topic.
ABAP knows date, time, and different representations of time stamps. And, it provides reuse functions to execute date and time calculations. OData has its own date and time definitions with functions that may be used in
$filter expressions, for example.
Often, questions arise how the different representations map and how a data provider needs to be implemented to create a best match between both worlds. It is certainly not possible to dig into each and every detail. But, let us start with a few aspects.
Date and Time in OData
To represent date and time information, the OData specification in version 2.0 knows the three primitive types
- Edm.Time, and
Since this is not really sufficient for business applications specification version 4.0 switches to
- Edm.DateTimeOffset, and
We focus on specification version 2.0 and restrict the discussion to Edm.DateTime and Edm.DateTimeOffset. Details regarding the representations can be found in the OData specification, the ISO 8601 standard, and in http://www.w3.org/TR/xmlschema-2. Here, we quickly list the different formats. The literals are used in the URI, that is, in
$filter expressions or key predicates, for example. ATOM and JSON refer to the content type of the request or response payload. The number of decimal places available for sub-seconds is defined by the facet precision.
Edm.DateTime represents a date and a time in UTC (formerly, Greenwich Mean Time):
|JSON||“\/Date(<ticks>)\/”<ticks> = number of milliseconds since midnight Jan 1, 1970||“\/Date(1467981296000)\/”|
The ticks in the JSON representation may also be negative to describe dates and time before Jan 1, 1970. “\/Date(-6847804800000)\/” is midnight Jan 1, 1753, for example.
Edm.DateTimeOffset adds time zone information relative to UTC. The date and time information is amended by the standard time difference (offset) with the sign v: +01:00 for Central European Time (CET) or -05:00 for Eastern Standard Time (EST), for example.
|JSON||“\/Date(<ticks>[“+” | “-” <offset>)\/”<ticks> = number of milliseconds since midnight Jan 1, 1970<offset> = number of minutes to add or subtract||“\/Date(1000+0060)\/”|
The character Z at the end of the literal or ATOM representation refers to UTC. Hence, datetimeoffset’2016-07-08T12:34:56Z’ and datetime’2016-07-08T12:34:56′ are equivalent.
Date and Time in ABAP
ABAP provides two predefined data types to handle dates (
TYPE D) and times (
TYPE T). Additionally, there is a data element
SYST_TZONE (an integer) to describe a time zone as the time difference to UTC in seconds. Hence, date and time information is being split up into several fields.
But there are also options to handle time stamps in single fields and to use those fields for time stamp calculations (see ABAP class
CL_ABAP_TIMESTAMP_UTIL, for example). The following data elements represent timestamps. While they use different underlying data types they are all constructed as a concatenation of year, month, day, and time of day.
|Data Element||Data Type||ABAP Type||Example|
|TIMESTAMP||DEC 15||P LENGTH 8||20160708123456|
|TIMESTAMPL||DEC 21,7||P LENGTH 11 DECIMALS 7||20160708123456.1234567|
|TZNTSTMPSL||NUMC 15||N LENGTH 15||020160708123456|
|TZNTSTMPLL||NUMC 21||N LENGTH 21||201607081234561234567|
Still, any time zone information needs to be kept separately.
This separation is the main issue when trying to translate date and time information between ABAP and OData. In general, the OData Library in SAP Gateway Foundation is only able to serialize one ABAP field into one OData primitive property and to de-serialize a property into one ABAP field. Separate timestamp and time zone information can neither be combined into a single Edm.DateTimeOffset property nor can such a property be split up into several ABAP fields.
As a result, any content of a property of type Edm.DateTimeOffset in an OData request payload or in the request URI is converted and provided to the data provider of the service in UTC. The time zone information gets lost. An OData response created by SAP Gateway Foundation may only expose date and time content in UTC. Time zone information is not added in the OData response since it is not clear which time zone is supposed to be used.
The usage of conversion exits (see ‘Conversions in SAP Gateway Foundation – Part 1‘) to split Edm.DateTimeOffset information or combine ABAP fields is not possible. SAP Gateway Foundation does not provide any means to model such relationships.
Hence, time zone information can only be handled by the data provider of the service. It is not possible to expose it correctly as part of an Edm.DateTimeOffset property but only as a separate property of type Edm.Int32, for example. The client implementation would need to implement the translation from and into a desired representation.
Therefore, we can stick to Edm.DateTime when summarizing the mapping in a small example. The following screenshots show a sample piece of implementation in
DATA: BEGIN OF ls_entity, key TYPE int8, dt_date TYPE d, dt_dts TYPE timestamp, dt_nts TYPE tzntstmpsl, dt_dtsl TYPE timestampl, END OF ls_entity. ls_entity-key = 2. " Date " TYPE D ls_entity-dt_date = '17530101'. "YYYYMMDD " short timestamp in decimal (DEC 15) and numerical (NUMC 15) format " TYPE TIMESTAMP ls_entity-dt_dts = 20160708123456. "YYYYMMDDhhmmss " TYPE TZNTSTMPSL ls_entity-dt_nts = 20160708123456. "YYYYMMDDhhmmss " long timstamp in decimal (DEC 21,7) format " TYPE TIMESTAMPL ls_entity-dt_dtsl = '20160708123456.1234567'. "YYYYMMDDhhmmss.fffffff " TYPE TZNTSTMPLL " cannot be used
The corresponding ATOM response would be:
And, a JSON response looks like:
Please observe that SAP Gateway Foundation is not able to handle ABAP fields of type
TZNTSTMPLL. It can only translate numerical timestamp information of length 15 providing the information to the OData Library as packed number of length 8. If you need to use sub-seconds please choose the decimal representation
DEC 21, 7 (data element
TIMESTAMPL) in your service implementation.
Dates are exposed with time 00:00:00. And, if the number of decimals defined by the precision of Edm.DateTime or Edm.DateTimeOffset is lower than the internal number of decimals for sub-seconds the remainder is cut off – no rounding takes place. Similarly, trailing zeros are added to the ticks in the JSON representation if required and micro-/nano-seconds are cut off.
OData version 2.0 initially specified Jan 1, 1753 as the lower boundary for dates to avoid ambiguities with the Julian Calendar while version 4.0 just refers to the Proleptic Gregorian Calendar (including projected dates before Jan 1, 1583) but also allows for year 0 and negative years. In any case, the initial values of the ABAP data types do not have a corresponding representation in Edm.DateTime.
Therefore, the OData library in SAP Gateway Foundation serializes an initial ABAP date / time value to
NULL in the OData response if the corresponding property is nullable. If the property is not nullable an exception is raised that results in an error response.
Additionally, SAP Gateway Foundation does not take any potential lower boundary into account but just translates the ABAP value. Decimal value 9121123000000 would result in an ATOM representation
0912-11-23T00:00:00 – the birthday of Otto I.
We have seen that the JSON representation works with milliseconds relative to midnight Jan 1, 1970. The type is a JSON string. In such a JSON string, SAP Gateway Foundation also accepts the ATOM format of Edm.DateTime or Edm.DateTimeOffset in request payloads.
None of the OData date and time representations includes a daylight savings time indicator. Times between 2:00 and 3:00 in the night when daylight savings time ends cannot be expressed without ambiguity.
Let us finish with a remark on conversion exits. With the former SAP GUI is was sometimes necessary to format timestamps into a kind of external representation. Conversion exit
TIMES does such a conversion and might be attached to a timestamp domain in the ABAP Dictionary. Not only does this exit convert the timestamp with system time zone
SY-ZONLO it also creates a character-like representation using the
WRITE statement (please refer to the ABAP documentation). Such a representation can neither be handled by SAP Gateway Foundation nor does it make sense to work with such a representation that has nothing to do with the OData format. Therefore, please make sure not to use domains with those conversion exits or to switch off the conversion as explained in ‘Conversions in SAP Gateway Foundation – Part 1‘.
 Or TZNTSTMPS
 Or TZNTSTMPL
I like history and I especially like the example of using the birthday of Otto I. ??
Is there a way we can convert JSON timestamp into SAP?
Currently in my webservice response I am getting date as
I have to convert it to YYYYMMDD ABAP format.
I am not aware of a re-use function that would do the job. So, I guess you need to write your own piece of ABAP.
to extract the ticks and the offset from the JSON representation. Then calculate the seconds from lv_ticks considering lv_offset, divide by 86400 as an integer (using DIV) and add this to 19700101 to get the date.
hey Kethan, I don't convert at this low level, using sap gateway but try to use the statement CONVERT ... that's what I use
In SAC feed via odata i have the output field from a CDS view "posting date". It shows as a AA type "character field not a "date" field . I am not an abaper, but I can navigate CDS views. I think the field started as a date field in the abap table .. but where how do i tell an abaper to fix the code to make it a date field so I can use it for incremental loading.
It should be checked how the corresponding field is typed in the CDS View that is the basis for the OData exposure. Typing may change when views are stacked on top of the original database table. Then, the question is how the OData service was created: manual implementation on top of the CDS View, auto-exposure?
THanks so much.. for responding .. just to keep it clean the view in question is the SAP delivered C_RevenueFromInvoiceQry view. It has various anotations that should drive odata to make a date field .. Like @Semantics.systemDate.createdAt: true and the tables have dats fields..
The service is generated upon activation of the CDS view with ODATA publish true annotation. I have heard rumors of another way but I am resisting that because I don't know it!!! and itseems dumb that an annotation does not exist that would drive the odata to have a date field.. 🙂
If I have to do something different to get the date field exposed.. can you point me as if I was wearing very thick glasses to how to do it set by step.. it would be very appreciated. SAC documentation should mention this .. it must be driving hundreds of people nuts!
Hello Ned. I am afraid I cannot help with that specific aspect. I can see that the OData service is an auto exposure of the mentioned CDS View and indeed provides the billing date as Edm.String. I suppose this is intended - OData V2 does not know a proper date type, it just uses Edm.DateTime which might not be suitable in this case. I am neither an expert in the analytics space nor in that specific area.
Maybe it is a good idea to post the topic in the 'Answers' section as a question where it could get appropriate attention. Sorry. Thomas
Again thanks for reply.. can you lead me to where you can manually alter the CDS generated service and override the edm.string to edm.datetime?
You have to build an OData Service using the Referenced Data Source appraoch.
Here you can override the define( ) method in the MPC_EXT class as described here for another used case.
thanks for the reply.. I can't tell you how long I have been trying to find this out.!!Seems crazy that SAC would highlight this feature for incremental loading yet not mention any links to this. Are you 100% sure this is the only way... Why do decimals output as the correct EDM type by not dates? Why is there not a cds anotation that does this by now 🙁 ..
Now that I have vented.. I am not really a coder.. but having worked for SAP for 23 yrs before retiring ) I am a good hacker.
It seems that the great blogs you wrote might be dated? but I ma very nervous to procede 🙂
1) So I go to segw then create a project with the option "service model for ref service"
2) choose the original service then it look like I can use this screen to just force a change to the entity type...
Does this seem like the correct thing to do.. ( venting again - why is is so hard:)
It seems to stop on me... when I get to the screen to to select the ref data source wizard the error it gives me is "CDS view XXXXXx is intended for analytics and prevents me form using it as a ref data source.
ANy more docs you can porvide??
I am currently working on a OData v2 service implementation where a Function Import has (among else) one input parameter with data type Edm.DateTimeOffset
It seems SAP Gateway can't handle positive UTC offsets for the datetimeoffset'...' function.
datetimeoffset'2018-01-02T09:15:00Z' arrives as 20180102091500, as expected.
datetimeoffset'2018-01-02T09:15:00-06:00' to represent the same local time in Houston (Texas, USA) arrives as 20180102151500, which is also as expected.
datetimeoffset'2018-01-02T09:15:00+01:00' to represent the same local time in Stavanger (Norway) arrives as 20180102091500, which is not at all as expected.
As far as I have understood the documentation I have found positive and negative offsets up to 14:00 [-14:00, 14:00] is what the standard defines. SAP Gateway seems to only handle negative offsets, but without input validation, so datetimeoffset'2018-01-02T09:15:00-24:00' arrives as 20180103091500 and even datetimeoffset'2018-01-02T09:15:00-99:00' is accepted and arrives as 20180106121500.
Hi Kjetil... sorry for the late reply. I didn't have the chance to check that but I would propose to open an incident with respect to this issue. Could be a bug. Thomas
I would if I could... Unfortunately I am not allowed to, because that requires that my S-user is "linked to" a SAP installation. It is not. However, I can prepare the case and ask if one of the employees here will do it.
ok thanks Thomas, I have a question since I can't seem to make it work in odata 2 with sap GW. How do I write a filter that takes a timestamp? I'm trying to select all bookings from a certain date to the hi-date - and I'm testing it in the sap gateway client:
Bookings?$filter=CreateDate eq '2018-01-17T23:00:00' - invalid token in position 14
And sometimes it works when I just do this, but sometimes it doesn't:
Bookings?$filter=CreateDate eq DateTime'2018-01-17T23:00:00'
I can swear just a few minutes ago I was getting the error invalid toke in position 22 for the statement above... could it have something to do that I was copying it from an email text, I wonder - because it was working yesterday evening. The only difference is that I deleted the DateTime part before and wrote it by hand.
your second URL snippet comes close. Date and time in $filter require the URI literal form - see http://www.odata.org/documentation/odata-version-2-0/overview/#AbstractTypeSystem.
$filter=CreateDate eq datetime'2000-12-12T12:00'
should be fine.
Thanks! It all works now. Just as a hint to others I used the statement CONVERT DATE... to generate the timestamps too if needed...
I tried to "like" your comment but it doesn't work.
hi KETHAN UPPALAPATI ,
am telling the scenario that i followed .
i make the data type of that date field from DATS to CHAR8 .
in this case date will come like 20181904.
for the frontend team it is easy to convert the above date field according to their requirement.
Thanks, Kethan Uppalati. Of course, it is possible to send plain data in a certain format to the client and let the formatting happen on client-side.
But at the end, the OData service will only expose strings somehow foiling the elaborate type system of the OData standard.
I want the opposite, as others have posted I don't understand why the CDS view accessing a DATS ABAP field outputs EDM.STRING when the odata service is executed and the meta data is reviewed.
When the CDS view is used to send data to SAC there is a feature that relies on DATE time fields to do an incremental load.. this is critical ..
How can this be set correctly ? is there a semantics tag?
Not sure what the issue is with date. I’ve just passed Datetime field and in the debug mode, it shows that time portion has been auto-magically filtered out. BTW, my Netweaver stack is 7.51 SP2.
SAP Gateway Client with OData based out of CDS View with Association.
And debug mode of method FLIGHTSET_GET_ENTITY.
Filter working fine,
Filter in debug mode,
Hello Kyo Choi,
It is a bit difficult to analyze from a distance. But I assume you exposed a CDS view containing an attribute type as date (ABAP built-in type D).
The blog describes that OData V4 introduces Edm.Date but OData V2 does not provide an appropriate data type for dates. Hence, the Gateway Foundation needs to map the ABAP date to Edm.DateTime. The metadata of the service shows Fldate as Edm.DateTime and both the key as well as the filter string need to an appropriate literal representation.
But the Gateway Foundation framework knows that the internal representation had been an ABAP date and automatically maps accordingly. The service provider already gets the internal representation. This is the magic you are referring to. And, the mapping simply cuts of the time information.
Very good blog, thanks Thomas! I have one question: is it the idea that you perform the value conversion to UTC in ABAP code, and the OData only does the displaying in Epoch?
When we have a Timestamp in ABAP for our timezone, OData displays it in epoch but the value is our timezone value, not the adjusted value to UTC. I would then have thought that SAP always does the UTC value (timezone) conversion in out and out in.
Thanks a lot. Sorry for the late reply. If I understand the question correctly: no, you do not need to convert in ABAP.
If the client sends an OData request that specifies a timezone ≠ UTC then the OData library in the SAP Gateway Foundation framework will convert to UTC. The data provider class you would implement already gets the converted date/time in the respective ABAP format.
If you return date/time data from your data provider class to the framework then this date/time will always be interpreted as if it would be UTC - without any conversion. Even if you stored another timezone somewhere in your database table you will not be able to provide this information to the Gateway framework.
But: if you are able to handle time zones somehow because you store them separately and a proper handling at the client side is available then you would need to do conversions in your own ABAP code.
I kept data type as dats in my database table while creating (post method) a new record in OData. It was created when using xml format but not in json format.
Is there any way we can convert JSON timestamp into SAP?
Currently in my webservice response I am getting date as
I have to convert it to YYYYMMDDHHSS ABAP format.
In which context do you want to covert this information? If you specify a json payload for an OData service that is implemented with SAP Gateway Foundation, then the OData library and the Gateway framework automatically convert the data. You receive the information in the data provider class already in an ABAP format depending on the definition of your OData service.
I receive timestamp from backend in long format (ABAP Type TIMESTAMPL).
Calling the method getMilliseconds of Edm.DateTimeOffset I make sure that correct value of milliseconds is received.
Then I want to send the very same timestamp value to the server via ODataModel.update (v2) method. My problem is that milliseconds part is being cut from the value. Example:
expected value 20191126130221.5254400
got on server 20191126130221.5250000
Would you have a suggestion how can I fix it?
Component: SAP_GWFND 751 0007 SAP Gateway Foundation 7.40
Ping Thomas Nitschke
I am not sure what type of OData client you are using. For OData V2, the situation is as follows:
The blog post also describes a special feature of the OData library in SAP Gateway Foundation. You can transfer an ATOM XML representation of DateTimeOffset (2019-11-26T13:02:21.5254400Z) in the value of the DateTimeOffset property in a JSON payload. Then, the result is also as expected. But this is a specialty that would need to be supported by the client.
Thank you for quick response. I have mistaken nanoseconds for milliseconds. As workaround, I will round nanoseconds on backend, as we do not really need such precision.
It is quite interesting to use 7 digits for microseconds especially because python's datetime.strptime  format fails to parse Edm.DateTime values with formatting string "%Y-%m-%dT%H:%M:%S.%f". The code "%f" accepts 1-6 digits and the 7th digit is reported as an leftover.
I have to come up with a fix for our PyOdata .
What are your suggestions?
Can we just ignore those hundreds of nanosecond?
That's an interesting topic. I am not an expert in all the specifications but it seems that both ISO 8601 as well as RFC 3339 allow for any number of digits as fractional seconds. The specifications point out that the communication partners must agree upon the usage of fractional seconds.
As far as I can see in https://docs.oasis-open.org/odata/odata/v4.01/cs01/abnf/odata-abnf-construction-rules.txt, OData restricts fractional seconds to 12 (!) digits. The 7 digits rather seem to be a restriction imposed by the ABAP data type - might need to correct the blog post a little.
Ignoring extra digits is probably an option. Will try to get some more background information but can't promise.
Greate blog, really helpful!
I'm experiencing some issues though... When trying to create a POST test data for timestamps (DEC15) inside the gateway client.
No matter how I format the the dates it will always give me either cx_parameter_invalid_range with the details of "Property 'X' at offset 'Y' has invalid value 'Z'".
Is there a way to pass that data when doing testing through the gateway client?
Thanks a lot. And, sorry for the late reply.
The exception points to the ABAP OData Library. So, I suppose there is an issue with the data format you use in the payload of your POST request.
TIMESTAMPS should understand date + time up to seconds. Hence, an OData representation in XML would like 2020-06-09T08:13:00 and in JSON \/Date(1591683205)\/
Please check the format of you request payload (XML or JSON) and try with examples above. If I does not work, someone would need to look into details in your system.
Hello Thomas Nitschke
I am facing a bit of a problem with mapping edm.datetime.
But the thing is I can't edit anything in ABAP Type editor. For some strange reason I cant switch it from Display to Edit mode (So I cant simply change Type kind to PACKED.
I know I can do this in Odata v4 but I want to resolve this problem in v2.
I know this is an old post but I'm hoping for a quick reply. Thanks in advance.
you can do the settings in the DEFINE method of your MPC_EXT class or the entity type specific define method.
Thank you Andre Fischer for a quick reply,
thing is I am fairly new to SAP let me check if I understood this correctly.
I should past this code that you sent in here:
or should I actually redefine it bc it clearly says never to modify this class.
This is a 'warning' in SEGW. You need to check what you want to achieve with the service and how the model in ABAP looks like.
If you have and want a date in your ABAP structure typed with the ABAP-internal type 'd', then the only possibility in OData V2 is a mapping to Edm.DateTime. But since this Edm type is able to transfer more information than just a date, the SEGW issues this 'warning'. It can be ignored in this case.
It is a different story if you want to handle the complete date & time information in the backend - not just the date part. Then, a proper ABAP type must be chosen.
The actual steps (see comment from Andre Fischer) then depend a bit on the service and the way it was created: manual, mapped data source, referenced data source... This goes a bit too far in the comments.
hi @thomas Nitschke,
Hello. I am not sure if I understand the question. As far as I remember, there is no restriction in OData which properties of an entity type may represent its key. So, also a date property (Edm.DateTime in OData V2) might work. The representation of the backend date (ABAP type d) is straightforward - see above.
In the URL of course, you need to use the literal representation - see table above.
In the MPC class you already made Matnr a key. Just try to do it the same way for 'Ersda'. The code you commented repeat 'create_property' but you already created the properties above.
This type of question is best raised in the 'Answers' section of the SAP Community. Developers check that area quite frequently and you may get a more targeted answer if colleagues stumbled across similar issues. They could provide their solution or hints how to solve an underlying problem.
Hi Thomas Nitschke, Hi community,
recently I had an issue with a Edm.DateTime conversion in an OData V2 property. In some cases the OData service implementation returned value 0 as ABAP timestamp for this property. In our production system this caused an /IWCOR/CX_DS_EDM_FACET_ERROR SAP Gateway error, because "Value '0 ' violates facet information 'nullable=false'".
Thanks to this blog post, I understand how the conversion works and that nullable needs to be set to true in the OData Entity. The issue is now fixed and now also a correct initial timestamp ('00000000000000') is used.
However I'm still wondering, why we don't get this SAP Gateway error in our test system. Both systems are on the same software versions. The test system seems to ignore the conversion error or does not do a check at all.
Does anybody now, why these systems behave different? Is there any system setting to disable these checks?
Hello Swen Koenig,
Thanks for the question. At least, I am not aware of such a system-dependent difference. Strange. Maybe you try to re-post the question in the 'Answers' section of the community.
we are facing a requirement that sounds pretty simple:
Calling an existing oData-Service with the current date.
At least with the year-ID (YYYY) of current date.
static version that works:
....$filter=YearCreated eq ‘2023’
but we need it dynamically:
....$filter=YearCreated eq year(now())
=> unfortunatelly the dynamic version does not work.
Do you have an idea how to derive current year
to pass it as a filter value to odata services?
Nice example. I do not have an immediate answer - most probably those dynamic functions are not implemented. I forwarded the question to a colleague.
But you might want to raise it in the 'Answers' section of the community as it gets more attention.