Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have the following TimeStamp format in my Source Data
2019-10-10T23:08:00+01:00
The format in detail has the following structure
YYYY-MM-DDTHH:MM:SS+The difference from my current TimeZone.
The T and + are just seperators and need to be eliminated.
My Expected Result fields from the above data are:
Date: 2019-10-11
Time: 00:08:00
The tricky part for me here is to update the Date and Time based on the TimeZone difference (+01:00) in the example data.
Any Quick Suggestion here?
Please go through the last comment here. Should explain you everything instead of reading the whole thread.
May be this
Time:
LOAD *,
Date(Floor(EventTimeStamp + Sign*AdjustTime)) as EventDate,
Time(Frac(EventTimeStamp + Sign*AdjustTime)) as EventTime;
LOAD ID,
TimeStamp(TimeSTamp#(Replace(Left(Event, Len(Event) - 6), 'T', ' '), 'YYYY-MM-DD hh:mm:ss')) as EventTimeStamp,
Interval#(SubField('+' & Replace(Event, '-', '+'), '+', -1), 'hh:mm') as AdjustTime,
If(SubStringCount(Event, '+') = 1, 1, -1) as Sign
Inline [
ID,Event
1,2019-10-10T14:08:00+00:00
2,2019-10-10T23:08:00+01:00
3,2019-10-10T04:08:00-05:00
]
;