Zoho Deluge: Convert Xero Date (Unix Timestamp) to Standard Date String

: Category: Zoho; 04 May 2020; Hits: 50768

What?
So this is a super quick note that I'll probably remember anyway but just in case, I'm writing this article so I don't spend time researching it later.

Why?
I'm synchronizing Xero Invoices with Zoho CRM Invoices and noticed that Xero stores its dates in Unix Timestamps.

How?
We're going to filter out the unix seconds from the date provided by Xero then apply a toTime() function to it.

copy raw

v_XeroTime="/Date(1586995200000+0000)/";
v_StartIndex = v_XeroTime.indexOf("(")+1;
v_EndIndex = v_XeroTime.indexOf("+", v_StartIndex);
v_UnixSeconds = v_XeroTime.subString(v_StartIndex, v_EndIndex);

info v_UnixSeconds.toLong().toTime();
// yields 15-Apr-2020 17:00:00

v_XeroTime="/Date(1586995200000+0000)/";
v_StartIndex = v_XeroTime.indexOf("(")+1;
v_EndIndex = v_XeroTime.indexOf("+", v_StartIndex);
v_UnixSeconds = v_XeroTime.subString(v_StartIndex, v_EndIndex);
info v_UnixSeconds.toLong().toTime();
// yields 15-Apr-2020 17:00:00

Chained

copy raw

v_XeroTime="/Date(1586995200000+0000)/";
v_ZohoTime = v_XeroTime.subString(v_XeroTime.indexOf("(")+1, v_XeroTime.indexOf("+")).toLong().toTime();
// yields 15-Apr-2020 17:00:00

v_XeroTime="/Date(1586995200000+0000)/";
v_ZohoTime = v_XeroTime.subString(v_XeroTime.indexOf("(")+1, v_XeroTime.indexOf("+")).toLong().toTime();
// yields 15-Apr-2020 17:00:00

Using a regular expression

copy raw

v_XeroTime="/Date(1586995200000+0000)/";
v_ZohoTime = v_XeroTime.replaceAll("[^0-9]", " ", false).trim().toList(" ").get(0).toLong().toTime();
// yields 15-Apr-2020 17:00:00

v_XeroTime="/Date(1586995200000+0000)/";
v_ZohoTime = v_XeroTime.replaceAll("[^0-9]", " ", false).trim().toList(" ").get(0).toLong().toTime();
// yields 15-Apr-2020 17:00:00

Update 2025
A few more years and a few more systems later, we need to parse out the timezone so its reusable, and also need to account for when timezone is not included in the Xero date/time:

copy raw

// REMINDER: Zoho Deluge Tryout needs backslashes to be escaped, CRM regex does not

// if timezone is included (can be negative or positive):
// deluge parse and convert to time
v_XeroTime = "/Date(1712222830083+0100)/";
v_ZohoEpoch = v_XeroTime.replaceAll("/Date\((\d+)([+-]\d{4})\)/","$1",false);
v_ZohoTZ    = v_XeroTime.replaceAll("/Date\((\d+)([+-]\d{4})\)/","$2",false);
v_ZohoTime = v_ZohoEpoch.toLong().toTime("yyyy-MM-dd HH:mm:ss", v_ZohoTZ);
info v_ZohoTime;

// check if timezone was included or not
v_RegexPattern = "/Date\((\d+)([+-]\d{4})?\)/";
v_ZohoEpoch = v_XeroTime.replaceAll(v_RegexPattern,"$1",false);
info v_ZohoEpoch;
// extracting TZ (eg. +0100) for CRM but otherwise has no effect on .toTime() 
v_ZohoTZ = v_XeroTime.replaceAll(v_RegexPattern,"$2",false);
info v_ZohoTZ;
v_ZohoTimeZone = if(isBlank(v_ZohoTZ), zoho.currenttime.toString("Z"), v_ZohoTZ);
info v_ZohoTimeZone;
v_ZohoTime = v_ZohoEpoch.toLong().toTime("yyyy-MM-dd HH:mm:ss", v_ZohoTimeZone);
info v_ZohoTime;
// IST is ambiguous (RFC 3339 / RFC 5322 :: use IANA instead)
v_IanaTZname = "Asia/Calcutta";
//v_IanaTZname = "Europe/London";
v_ZohoTime = v_ZohoEpoch.toLong().toTime("yyyy-MM-dd HH:mm:ss", v_IanaTZname);

// The best method is per client:
// Login to Xero > Accounting > Accounting Settings > Financial Settings > Time Zone
// convert to IANA TimeZone and hard-code this whenever querying Xero dates/times

// if hard-coding timezone from Zoho server (only takes long unix epoch seconds:
v_ZohoTime = v_XeroTime.replaceAll("/Date\((\d+)([+-]\d{4})?\)/","$1",false).toLong().toTime("yyyy-MM-dd HH:mm:ss", "Europe/London");

// REMINDER: Zoho Deluge Tryout needs backslashes to be escaped, CRM regex does not
// if timezone is included (can be negative or positive):
// deluge parse and convert to time
v_XeroTime = "/Date(1712222830083+0100)/";
v_ZohoEpoch = v_XeroTime.replaceAll("/Date$(\d+)([+-]\d{4})$/","$1",false);
v_ZohoTZ = v_XeroTime.replaceAll("/Date$(\d+)([+-]\d{4})$/","$2",false);
v_ZohoTime = v_ZohoEpoch.toLong().toTime("yyyy-MM-dd HH:mm:ss", v_ZohoTZ);
info v_ZohoTime;
// check if timezone was included or not
v_RegexPattern = "/Date$(\d+)([+-]\d{4})?$/";
v_ZohoEpoch = v_XeroTime.replaceAll(v_RegexPattern,"$1",false);
info v_ZohoEpoch;
// extracting TZ (eg. +0100) for CRM but otherwise has no effect on .toTime()
v_ZohoTZ = v_XeroTime.replaceAll(v_RegexPattern,"$2",false);
info v_ZohoTZ;
v_ZohoTimeZone = if(isBlank(v_ZohoTZ), zoho.currenttime.toString("Z"), v_ZohoTZ);
info v_ZohoTimeZone;
v_ZohoTime = v_ZohoEpoch.toLong().toTime("yyyy-MM-dd HH:mm:ss", v_ZohoTimeZone);
info v_ZohoTime;
// IST is ambiguous (RFC 3339 / RFC 5322 :: use IANA instead)
v_IanaTZname = "Asia/Calcutta";
//v_IanaTZname = "Europe/London";
v_ZohoTime = v_ZohoEpoch.toLong().toTime("yyyy-MM-dd HH:mm:ss", v_IanaTZname);
// The best method is per client:
// Login to Xero > Accounting > Accounting Settings > Financial Settings > Time Zone
// convert to IANA TimeZone and hard-code this whenever querying Xero dates/times
// if hard-coding timezone from Zoho server (only takes long unix epoch seconds:
v_ZohoTime = v_XeroTime.replaceAll("/Date$(\d+)([+-]\d{4})?$/","$1",false).toLong().toTime("yyyy-MM-dd HH:mm:ss", "Europe/London");

Additional

The reverse (date string to unix seconds) can be done using either of the following:
copyrawstyler
```
v_MyDate = "2020-04-01 12:34:56";
v_UnixSeconds = v_MyDate.toTime().toLong();
info v_UnixSeconds;

// or

v_UnixSeconds = v_MyDate.unixEpoch();
info v_UnixSeconds;
```
1. v_MyDate = "2020-04-01 12:34:56";
2. v_UnixSeconds = v_MyDate.toTime().toLong();
3. info v_UnixSeconds;
5. // or
7. v_UnixSeconds = v_MyDate.unixEpoch();
8. info v_UnixSeconds;
To normalize +0530 to include the colon +05:30, consider the following:
copyrawstyler
```
// Normalize to +HH:MM
offset = "+0530";
offset = offset.replaceAll("([+-]\d{2})(:?)(\d{2})", "$1:$3", false);
```
1. // Normalize to +HH:MM
2. offset = "+0530";
3. offset = offset.replaceAll("([+-]\d{2})(:?)(\d{2})", "$1:$3", false);
Error: Argument type mismatch -Found 'TEXT' but Expected 'Long' // Solution: Convert your string into a date.