Generate Date Time based on TimeStamp with TimeZone

[Sai33]

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.

[Sai33]

Any Quick Suggestion here?

Please go through the last comment here. Should explain you everything instead of reading the whole thread.

@sunny_talwar 

[sunny_talwar · Accepted solution]

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
]   
;