cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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 DataEntity Data

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

 

Liquid Code DataLiquid 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

3 REPLIES 3

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');

 

 

----------------------------------------------------
If you find this post helpful consider marking it as a solution to help others find it.
Anonymous
Not applicable

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

Hi Amit,

@OOlashyn's code appears to be working.

Current time IST:

justinburch_0-1599498575548.png

Current time UTC:

justinburch_1-1599498595320.png

Current time America/Panama:

justinburch_2-1599498614745.png

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.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (2,769)