ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Create JSON date field

Post new topic  Reply to topic
 Create JSON date field « View previous topic :: View next topic » 
Author Message
petervh1
PostPosted: Wed Sep 01, 2021 7:03 am    Post subject: Create JSON date field Reply with quote

Centurion

Joined: 19 Apr 2010
Posts: 122

I'm trying to create a JSON structure that contains (amongst other fields) the following:

Code:
"Field1StartDate":"/Date(-62135769600000)/",
"Field2EndDate":"/Date(253402214400000)/",


I don't see a mention of a date field in:
Quote:

https://www.ibm.com/docs/en/integration-bus/10.0?topic=domain-message-tree-mapping-in-json


How can I do this?

The value of Field1StartDate is a date minus the number of milliseconds since 1/1/1970, and Field2EndDate is 31/12/9999 minus the number of milliseconds since 1/1/1970.

Thanks
Back to top
View user's profile Send private message
timber
PostPosted: Wed Sep 01, 2021 11:58 pm    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

Converting a DATE to an INTEGER is not a JSON-specific task - that's why you did not find the information under the JSON-specific topics.

You have to do it like this:
Code:
DECLARE epoch TIMESTAMP '1970-01-01 00:00:00';
DECLARE secondsSince1970 INTERVAL (CURRENT_TIMESTAMP - epoch) SECOND;

DECLARE timeInMilliseconds CAST(secondsSince1970 AS INTEGER) * 1000;

I have not tested that code, so you may need to adjust some of the details to make it work.
Back to top
View user's profile Send private message
petervh1
PostPosted: Thu Sep 02, 2021 12:45 am    Post subject: Reply with quote

Centurion

Joined: 19 Apr 2010
Posts: 122

Thanks for that.

Am I correct in saying that I can't cast the millisecond value derived from the code you posted to a DATE format in the JSON output structure. (I need to send this JSON structure to SAP, and SAP expects a DATE type field here)
Back to top
View user's profile Send private message
timber
PostPosted: Thu Sep 02, 2021 5:20 am    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

I'm confused now. I thought you wanted to send a millisecond value in the JSON.

Please can you provide
- example input value(s)
- corresponding JSON output data, exactly as it needs to appear. Please use [code] tags (see buttons above) to ensure that it appears exactly as you intended.
Back to top
View user's profile Send private message
petervh1
PostPosted: Thu Sep 02, 2021 5:51 am    Post subject: Reply with quote

Centurion

Joined: 19 Apr 2010
Posts: 122

The JSON output needs to look like the Code section in my first post on this thread.

I just wanted confirmation that this cannot be a Date field type before I go back to the systems designer/analyst to say that this field is invalid in its current form.
Back to top
View user's profile Send private message
timber
PostPosted: Thu Sep 02, 2021 6:25 am    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

So you want your output JSON to look like this:
Code:
{
    "Field1StartDate":"/Date(-62135769600000)/",
    "Field2EndDate":"/Date(253402214400000)/"
}

It looks a bit strange, but it is a valid JSON document. So I don't understand what you mean by this:
Quote:
this cannot be a Date field type
and this
Quote:
this field is invalid in its current form


Are you absolutely sure that SAP wants this exact JSON format?
Back to top
View user's profile Send private message
petervh1
PostPosted: Thu Sep 02, 2021 6:43 am    Post subject: Reply with quote

Centurion

Joined: 19 Apr 2010
Posts: 122

Quote:
So you want your output JSON to look like this:
Code:
{
"Field1StartDate":"/Date(-62135769600000)/",
"Field2EndDate":"/Date(253402214400000)/"
}


Yes.

Quote:
this cannot be a Date field type


I meant that I can't CAST this as a DATE

Quote:
Are you absolutely sure that SAP wants this exact JSON format?


That's what I was given, but I'm starting to doubt it. Hence my initial query on this forum before I push back to the designer.
Back to top
View user's profile Send private message
timber
PostPosted: Thu Sep 02, 2021 10:30 am    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

I don't understand why you are asking about CASTing this value to an ESQL DATE. You already have a number which can easily be converted to a string of digits in the JSON output. Why do you want to turn it back into a DATE?

But I agree - you should be asking for a sample document or else the official SAP data format specification.
Back to top
View user's profile Send private message
fjb_saper
PostPosted: Thu Sep 02, 2021 8:21 pm    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY

And remember in Java the date under the form you show is a long not an int.
There are reasons for that, and you may need to use a double or float in ESQL.
Have fun
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
timber
PostPosted: Thu Sep 02, 2021 11:36 pm    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

In ESQL, an INT/INTEGER is a 64-bit value, so should be fine.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Create JSON date field
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.