Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am facing a problem for the below code:
[Data]:
load FILE_ID,
Timestamp(Timestamp#(UNPACKAGED_DATE_TIME,'DD-MMM-YY hh:mm:ss.fff TT'),'MM/DD/YYYY h:mm:ss[.fff] TT') as UNPACKAGED_DATE_TIME;
select FILE_ID, UNPACKAGED_DATE_TIME
from edi_e2e_user.abc_e2e_tracking_record where FILE_ID = 'ci1534054081616.19521239@sbycsaxy05_te';
when I execute the above code, I got nothing for UNPACKAGED_DATE_TIME, but when I hard coding date as
Timestamp(Timestamp#('11-AUG-18 08:38:01.000000000 PM','DD-MMM-YY hh:mm:ss.fff TT'),'MM/DD/YYYY h:mm:ss[.fff] TT') as UNPACKAGED_DATE_TIME
the data is coming well. Please suggest what should I do now.
Hi,
I am using the below code:
date(ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'Eastern Time (USA & Kanada)'),'MM/DD/YYYY')
but getting null value and if I am using the below code:
ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'Eastern Time (USA & Kanada)')
I am getting Eastern Time (USA & Kanada) text.
Please help me on this situation.
I just tried a few localization-strings and it seems that some don't work especially those ones which contain not only letters like () or - and I would consider it as a bug (either by parsing the strings or within the documentation). But using the following worked for me:
ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'GMT-09:30')
Beside this your date() formatting will just show the values as date but they remain a timestamp. If you want to get a date you need something like this:
date(floor(YourTimestamp)) as Date
respectively
time(frac(YourTimestamp)) as Time
to get the time. Quite often it's useful to split timestamps in this way to reduce the number of distinct values of a field.
- Marcus
Hi,
Your solution is worked for me:
ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'GMT-09:30')
but when I have used the below solution
date(floor(YourTimestamp)) as Date
and
time(frac(YourTimestamp)) as Time
it does not work and 11th Aug is cjamhed to 12th Aug.
Thanks,
Sandip
date(floor(YourTimestamp)) was just a simplification and you need to use your convert-expression instead of the YourTimestamp so that it should be look like:
date(floor(ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'GMT-09:30')))
- Marcus
Hi,
I am using the below code as per your suggestion:
Date(Timestamp(UNPACKAGED_DATE_TIME - (1/24*9.5)), 'MM/DD/YYYY h:mm:ss.fff TT') as UNPACKAGED_DATE_TIME,
ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'GMT-09:30') as Total_Date,
date(floor(ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'GMT-09:30'))) as DATE_ONLY,
time(frac(ConvertToLocalTime(UNPACKAGED_DATE_TIME, 'GMT-09:30'))) as Time_ONLY
are working fine for me. I think this is enough for my requirement.
Thanks,
Sandip
Hi,
One quick question from my end is when daylight savings will start the I have to change this?
'GMT-09:30' to another value?
With the above used manual offset of (1/24*9.5) there is no automatic to daylight savings like in functions like converttolocaltime() but you could calculate in also manually with something like:
(1/24*
(9.5+
if(daynumberofyear(UNPACKAGED_DATE_TIME) >= 80 or
daynumberofyear(UNPACKAGED_DATE_TIME) <= 265, 1, 0)))
The general logic should work but please check if used day-area is really correct in your timezone. Further it might need to be extended if it goes over several years and there are maybe leap years.
- Marcus
Hi,
Is there any general logic exists that will work for all the coming years and it will independent of day light savings or leap year etc? or the problem is occurring to me as because I am using materialized view in oracle?
I wondering that I am not facing problem for oracle but I am facing problem for view now.
Thanks,
Sandip
From a theoretical point of view functions like converttolocaltime() do cover all these possibilities but if they in your case don't work like expected - you could try it again maybe with some training-data for example querying the distinct timestamps of the last n years and applying on them the various qlik functions with all possible parameter-combinations - and you used a manual offset approach you will need to calculate it yourself whereby it mustn't be done everything within a single-expression. Daylight savings and leap years might be calculated within a master-calendar and their result could be mapped within an expression like above.
- Marcus