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.

[Stoyan_Terziev]

Hi Sai33,

That's extra tricky since you first need to extract the timestamp value of the string, then you need to add 1/24 of a day to reflect time zone, then you need to change the format so you can squeeze the words 'Date:' and 'Time:' in between.

Here is the script you need (edit so Time Zone is dynamic):

=
'Date: '&Date(
Timestamp(
Timestamp#(
Text(Date#(LEFT(
'2019-10-10T23:08:00+01:00', 10), 'YYYY-MM-DD'))&' '&
Mid('2019-10-10T23:08:00+01:00',12,8)
,'YYYY-MM-DD hh:mm:ss')+(Num(time(Right('2019-10-10T23:08:00+01:00', 4), 'hh:mm')))
,'YYYY-MM-DD hh:mm:ss'
)
)& ' Time: '&
Time(
Timestamp(
Timestamp#(
Text(Date#(LEFT(
'2019-10-10T23:08:00+01:00', 10), 'YYYY-MM-DD'))&' '&
Mid('2019-10-10T23:08:00+01:00',12,8)
,'YYYY-MM-DD hh:mm:ss')+(Num(time(Right('2019-10-10T23:08:00+01:00', 4), 'hh:mm')))
,'YYYY-MM-DD hh:mm:ss'
)
)

Kind regards,

S.T.

[Sai33]

Hi Stoyan,

Sorry for not being clear.

Date and Time are two seperate fileds i would like to generate from my SourceData field. 

Here, the tricky part for me is to update the date. If you observe the Date changes from 2019-10-10 to 2019-10-11.

[Stoyan_Terziev]

Hi Sai,

Ok, then it's even easier. The script I posted is plug and play so you can study how it works.

You can tweek it so instead of the piece where I subtract date, time and timezone from the whole string, you can simply imput the correct fields.

Yet - keep in mind you need to go through the part where you create a Timestamp value from the Date and Time fields and add the fraction of the day as numerical which is retrieved from the Zone field. That's how you fix the day from 10th to 11th.

And then you need to format it the way you want.

Kind regards,

S.T.

[Sai33]

Updated my original question to hopefully provide a better understanding

[Stoyan_Terziev]

Hi Sai,

Here it is in that case:

=
'Date: '&Date(
Timestamp(
Timestamp#([Date]&' '&[Time],'YYYY-MM-DD hh:mm:ss')
+(Num(Time('01:00', 'hh:mm')))
,'YYYY-MM-DD hh:mm:ss'
)
)& ' Time: '&
Time(
Timestamp(
Timestamp#([Date]&' '&[Time],'YYYY-MM-DD hh:mm:ss')
+(Num(Time('01:00', 'hh:mm')))
,'YYYY-MM-DD hh:mm:ss'
)
)

[Sai33]

Unfortunately, i'm struggling to achieve my Result here. Can you please go through the attached QVF file. 

Have also included the Expected Result as Comment.

[Stoyan_Terziev]

Attached.

Basically that's the first formula I posted.

Here it is with the script:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='de-DE';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan.;Feb.;März;Apr.;Mai;Juni;Juli;Aug.;Sep.;Okt.;Nov.;Dez.';
SET LongMonthNames='Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember';
SET DayNames='Mo.;Di.;Mi.;Do.;Fr.;Sa.;So.';
SET LongDayNames='Montag;Dienstag;Mittwoch;Donnerstag;Freitag;Samstag;Sonntag';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

Time:
LOAD 
	ID,
	Event
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
]   
;

Generated:
Load
	ID,
	Date(
		Timestamp(
				Timestamp#([Event],'YYYY-MM-DD hh:mm:ss')
						+(Num(Time('01:00', 'hh:mm')))
								,'YYYY-MM-DD hh:mm:ss'
				 )
		) as EventDate,
	Time(
		Timestamp(
				Timestamp#([Event],'YYYY-MM-DD hh:mm:ss')
							+(Num(Time('01:00', 'hh:mm')))
									,'YYYY-MM-DD hh:mm:ss'  
                  )
        ) as EventTime
       ,
       
'Date: '&Date(
Timestamp(
Timestamp#(
Text(Date#(LEFT(
[Event], 10), 'YYYY-MM-DD'))&' '&
Mid([Event],12,8)
,'YYYY-MM-DD hh:mm:ss')+(Num(time(Right([Event], 4), 'hh:mm')))
,'YYYY-MM-DD hh:mm:ss'
)
)& ' Time: '&
Time(
Timestamp(
Timestamp#(
Text(Date#(LEFT(
[Event], 10), 'YYYY-MM-DD'))&' '&
Mid([Event],12,8)
,'YYYY-MM-DD hh:mm:ss')+(1/24)
,'YYYY-MM-DD hh:mm:ss'
)
) as NewDesiredEventTime
Resident Time;

Results look like this:

[Sai33]

Thanks very much for your help, we are almost there.

For ID 3 you can see that the difference is -05:00. Here, we have to remove 5 hours from the value.

So, the expected result is 2019-10-09 23:08. 

With your script we got 10.10.2019 05:08 (First event in the Screen Shot) 

[Sai33]

I've almost cracked it in another not so elegant way. But, for me the + is not generating any results and the - is generating correct results.

Please have a look at the attached qvf.