convert date time field to UTC format

[lak]

Hello,
I need some help around converting the date field column.
I have a Customer response table coming from Redshift. The date time field is originally stored in UTC format.But when pulling it into Sisense, it is displaying in local timezone.
I wanted it to be displayed in the same UTC format as that in the original database.
 
For ex:  I have a column response_date, with the date in the backend DB as 
2023-08-11 22:09:56.86+00 , but when it comes to Sisense its displaying as : 8/11/2023, 03:09 PM ( which is in local timezone)

How to restore to the original UTC time format.

[rapidbisupport]

Hi Iak,

Is your instance self-hosted or Sisense Cloud?

[Sushant_Cropin]

We are a self-hosted environment, and we have the similar requirement, do we have a solution?

DateTime in the Source - 2023-01-10T08:28:27.404+00:00
DateTime in Sisense Cube - 1/10/2023, 08:28 AM

But there is no mention of the TimeZone in the Sisense, we can assume it is in UTC, but can we enable Sisense to actually store that TimeZone along with the DateTime?

[rapidbisupport]

Hi @Sushant_Cropin  and @lak,

There are a few issues at play with timezones. The first is which timezone the data exists in the Sisense cube, and this can be automatically offset by Sisense on import which can catch people out. Secondly there is the target timezone(s). Thirdly there is the relative filtering in Sisense (e.g. Today or This Month).

1. Firstly, our recommendation is to always set the server (at infrastructure level) to UTC. This is the default for all Sisense cloud hosted instances and if you alter this you get different timezone behavior than you might expect with offsets being applied on data import.

Test some of the dates in the cube to make sure that they have not been Offset during import by Sisense. If they have, the solution lies in the import query. You want Sisense to think the date or timestamp is already in UTC even if it is not (so that Sisense does not apply an offset). Alternatively you can bring into the cube and then use custom fields or tables to reverse the offset being applied (using the AddHours or Add Minutes functions). 

2. Supporting timezone per user (optional). Do as above but use an identifier in relationships to a dates table rather than an actual date (e.g. seconds since epoch). Then for each identifier you build out a table with what that datetime would be in each timezone. So your dates table would look like:

DateTimeKey, DateTime, Timezone
1704326400, 2024-01-05 00:00:00, 'UTC'
1704326400, 2024-01-04 11:00:00, 'AEDT'

This could be further optimized by using an integer to represent the timezone, then a separate table with each a timezone and the integer that represents it.
e.g.
Dates
DateTimeKey, DateTime, Timezone
1704326400, 2024-01-05 00:00:00, 1
1704326400, 2024-01-04 11:00:00, 2

Timezones
TimezoneID, Timezone
1, 'UTC'
2, 'AEDT'
Finally, apply RLS (Row Level Security) to assign each user to a single Timezone. This would best be done with adding users to a Sisense Group for their timezone.

3. Once you have the dates in there, if the user's timezone matches what is stored, you should get the expected dates/times on the front-end. This is with the exception of the relative filters (e.g. Today). These always work on UTC time, so if you are not in UTC when the day actually starts will differ from reality (in my case at UTC+11 I had to wait until 11am for the filter for Today to actually work as today instead of yesterday). As such, we have developed an Add-On that will make the relative filters work correctly. Happy to discuss.

This stuff can be quite complex. I hope this helps. Feel free to reach out.

RAPID BI
[email protected]

RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons