CODEWITHSUNDEEP
muledataweave
Jesse0451
Jesse0451

Reputation: 81

Convert java.sql.timestamp to DateTime in Dataweave

I am getting a java.sql.Timestamp from a database request. This value indicates an instant in time so timezone is not relevant (for that matter java.sql.Timestamp does not have a timezone, as it represents a global instant). I wish to use dataweave to convert this to an ISO8601 DateTime string, however dataweave is "adding" my local timezone (+10:00) to the value.

See Example:

%dw 2.0
output application/json
import java!java::sql::Timestamp
---

{
   epoch: 0 as DateTime,
   epochFromDatabase: Timestamp::new(0) as DateTime
}

where the result/preview is:

{
  "epoch": "1970-01-01T00:00:00Z",
  "epochFromDatabase": "1970-01-01T10:00:00Z"
}

You can see that the second value (epochFromDatabase) has +10 hours added to it despite being in UTC(Z).

Can this be corrected in dataweave only? I know I am able to correct this in java but I must only use dataweave/mule features.

-EDIT-- This is not a java issue as per example:

Timestamp epochTimestamp = new Timestamp(0);
System.out.println(epochTimestamp.toString());
System.out.println(epochTimestamp.toInstant().toString());
System.out.println(epochTimestamp.getTime());

Writes:

1970-01-01 10:00:00.0
1970-01-01T00:00:00Z
0

Such that toString does imply a local timesone (and not in iso8601), but when converted to an instant (whos toString defaults to ISO8601) it is correct.

Upvotes: 0

Views: 2035

Answers (3)

Vlad K
Vlad K

Reputation: 2841

This is looks like a defect in DW.
The DW mishandles conversions between java.sql dates and timestamps, treating them as LocalDateTime instead of DateTime.

This becomes evident when converting SQL dates to JSON, resulting in missing time zone information.

The problem originates from DW initially converting java.sql.Timestamp to LocalDateTime without accounting for the local time zone. Subsequent conversion from LocalDateTime to DateTime applies the local time zone shift.
This discrepancy causes a 10-hour shift in the final result, as observed in the given example.

Update: I've approached the MuleSoft support with this matter and been provided with the following astonishing code that would do the job:

(payload as String ++ now().timezone as String) as DateTime

This response indicates at least two things:

  1. The DW indeed has an issue with converting java sql timestamps to DateTime so the manual workaround is needed to explicitly specify the time zone for the conversion.
  2. The MuleSoft support believes that the time zone in the java.sql instance is always matching the DW/java one so they propose using "now().timezone" as a reliable way of retrieving the java.sql object time zone.

Upvotes: 1

Manish Yadav
Manish Yadav

Reputation: 300

I am just adding my thought here so that you can make your solution as per above comment;

The default format for a date time in DataWeave is actually ISO 8601. So we don’t need to specify the format string, and we don’t need to format both members separately.

I can not see anything here.

DataWeave 2.x

If you see anything like mismatch in date, Then you can try to define TimeZone like below.

%dw 2.0
output application/json
var utc = "UTC" as TimeZone
fun getFormattedDateTimeZoneBased()=(now() >> utc) as String
---
{
  myDate: getFormattedDateTimeZoneBased()
}

Upvotes: 0

aled
aled

Reputation: 25699

A DateTime always has a timezone. You can try a LocalDateTime that doesn't has one:

epochFromDatabase: Timestamp::new(0) as LocalDateTime

Output:

"epochFromDatabase": "1969-12-31T21:00:00"

Upvotes: 2

Related Questions