Date Time issue while fetching from CDS using Liquid Code and Time Zone Conversion via Moment JS

[Anonymous]

Hi Experts,

I am fetching the created on field from one entity via liquid code and trying to convert the date time to Panama timezone. but I am seeing one strange behavior while fetching records via liquid code i.e My Time zone id "Asia/Calcutta" i.e IST and I have inserted one record around 5pm today. below is a screenshot of the same:
Entity Data

But while fetching data from liquid code it is giving me 11:29 AM:

Liquid Code Data

Why is that so? Is this expected? I can see that it is converted to UTC i.e IST-5:30 hr. Please correct me if I am wrong.

And I am trying to convert the DateTime to Panama timezone using Moment.js, I am using below code but it is giving incorrect because I assume from liquid code I am getting incorrect value:

<script src="/MomentJS"></script>
<script src="/moment-timezone.min.js"></script>
<script src="/moment-timezone-with-data.min.js"></script>

<script type="text/javascript">
  
  $(document).ready(function() {   
    var data = $.parseJSON(
      $.ajax({
        url: '/JsonData/',
        dataType: "json",
        async: false
      }).responseText);
    
    //set local
    moment.locale('es-PA');

    var CreatedDate = data.UserData[0].UserData_CreatedOn;
    var date = new Date(CreatedDate);
    var dateUTC = moment(date);
    var datePanama = dateUTC.tz('America/Panama').format('MM/DD/YYYY h:mm A');
    $("#createdon").text(datePanama);
 });

below is the output of datePanama variable:
"08/13/2020 12:59 AM"

Any help will be appreciable on this.

Thanks,

Amit

[OOlashyn]

Hi @Anonymous ,

DateTime fields are always fun in CDS. Check out this docs to find more about their behaviour (the article is for On Prem version, but it is valid for Online as well).

In short - yes on the database level your date field is stored in the UTC timezone. So your liquid code will return you the date in UTC. But when you use new Date in your js code it will set it incorrectly as it will use your browser timezone instead of UTC.

To solve it I would do next:

First, update your liquid template to receive date in the format that moment js can parse easily for example:

{{ contact.createdon | date: "%F %H:%M" }}

this will format date like this: "2020-08-18 17:44". Find more about liquid date filter here.

Then, when you know it is UTC you can easily get what you need with moment:

var datePanama = moment.utc(data.UserData[0].UserData_CreatedOn).tz('America/Panama').format('MM/DD/YYYY h:mm A');

[Anonymous]

Hi @OOlashyn

Sorry for the late response and not sure how it was selected as a solution but I tried your approach. and it doesn't resolve the issue. 

When I use date filter i.e 

| date: "%F %H:%M"

Actual UTC created on date is like this:

CreatedOn": "7/6/2020 9:30:17 AM"

 when using the date filter, I am getting below output (I am not getting yyyy-mm-dd) :

CreatedOn": " 9:30"

 Then I tried with the below filter:

{{ item['User.createdon'] | date: 'g' | date: 'yyyy-MM-dd %H:%m' }}

It gave below output:

CreatedOn": "2020-07-06 9:30"

But after that when I apply Moment.js code to convert the UTC time to Panama time zone, it is not working.

Basically, I am in the IST timezone, so, if the CDC store date in UTC and while fetching using liquid & fecthxml code, it will return date in UTC date timezone then I want that to be converted to Panam timezone i.e -5:30 hrs irrespective of person current timezone. i.e for me also it should do -5:30 hr, not -10:30 hrs which currently happening.

Any help will be appreciable.

Thanks,

Amit

[justinburch]

Hi Amit,

@OOlashyn's code appears to be working.

Current time IST:

Current time UTC:

Current time America/Panama:

Your last comment of "it will return date in UTC date timezone then I want that to be converted to Panam timezone i.e -5:30 hrs irrespective of person current timezone. i.e for me also it should do -5:30 hr, not -10:30 hrs which currently happening" is a little off - for you to be -5:30 would not be Panama's time zone, it would be UTC's time zone

1. The date/time returns in UTC, regardless of user's time zone - for you it changes 10:39 pm to 5:09 pm, for me it changes 10:09am to 5:09 pm
2. Moment.js changes the date/time to America/Panama (EST), which is -5 hours from UTC, so from 5:09pm to 12:09pm

I hope this clears things up.