topic Re: Convert EST Expression to Local Time Zone (CST) in QlikView

Convert EST Expression to Local Time Zone (CST)

haneeshmarella — Wed, 25 Nov 2020 16:16:04 GMT

I use an expression to get the datetime out of two separate numeric fields of date and time which is EST. How do I convert them to local timezone of Central US time? The expression I use gives me time in EST.

The expression I use is

Timestamp((DATE) + (Interval(Floor(Time#(Num(TIME_STRING, '000000'), 'hhmmss'),1/24), 'hh'))

,'MM/DD/YYYY hh TT')

I tried two methods to get it in CST but it did not give me the desired result:

1. I used the ConvertToLocalTime function but it messes up the times. Probably I need to convert into some standard format before applying this? Not sure.

2. I subtract an hour from above expression using -1/24,'MM/DD/YYYY - hh TT'), but the problem here is when I create a table with the datetime field, I get 11 PM of the previous day in the list when I should show it from 12 AM today (1PM EST) instead as data is technically 'today's data'.

Please let me know if there is an alternative method.

Re: Convert EST Expression to Local Time Zone (CST)

BalaBhaskar_Qlik — Sat, 04 Aug 2018 13:05:21 GMT

May be try with these:

Examples:

ConvertToLocalTime(’2007-11-10 23:59:00’,’Paris’) returns ’2007-11-11 00:59:00’ and the corresponding internal timestamp representation.

ConvertToLocalTime(UTC(), ’GMT-05:00’) returns the time for the North American east coast, e.g. New York.

ConvertToLocalTime(Date, 'GMT-06:00') as DateConverted;

converttolocaltime(converttolocaltime(fieldname,'UTC'),'Pacific Time (USA & Canada)');

ABC:

LOAD *,

ConvertToLocalTime( Date, 'Central Time (US & Canada)') as DateConverted;

LOAD *,

Date#( SDATE, 'YYYY-MM-DD hh:mm:ss') as Date;

You could try it with the function converttolocaltime() but if you have only a few timezone offsets then you could simply add the hour-offset to your LoginTime, like:

LoginTime + (1/24 * pick(match(TimeZone, 2, 3), 5, -7))

Check this:

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/converttolocaltime.htm?q=converttolocaltime