Converting time zone of dateTime field and saving it in another field within a workflow

Avatar

Avatar
Give Back 5
Level 3
marcinkacz
Level 3

Likes

19 likes

Total Posts

46 posts

Correct reply

2 solutions
Top badges earned
Give Back 5
Give Back 3
Give Back
Ignite 1
Validate 10
View profile

Avatar
Give Back 5
Level 3
marcinkacz
Level 3

Likes

19 likes

Total Posts

46 posts

Correct reply

2 solutions
Top badges earned
Give Back 5
Give Back 3
Give Back
Ignite 1
Validate 10
View profile
marcinkacz
Level 3

28-01-2019

Hi,

I built custom data schema in order to collect and export selected records from nms:trackingLogRcp and nms:broadLogRcp. One of the column is DateAndTime. It contains eventDate (if record comes from broadLogRcp) or logDate (if record comes from trackingLogRcp). Before export this data I would like to change time zone of DateAndTime field and save it in DateAndTime2. I can do it by using loop in JavaScript activity and formatDate method and xtk.session.Write(). Unfortunately this solution is very time consuming. Processing of 1000 records takes around 5 minutes.

Do you know more efficient approach? Maybe some SQL script will help or some batch process?

Thank you,

Marcin

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Establish
MVP
wodnicki
MVP

Likes

977 likes

Total Posts

1,096 posts

Correct reply

516 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile

Avatar
Establish
MVP
wodnicki
MVP

Likes

977 likes

Total Posts

1,096 posts

Correct reply

516 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile
wodnicki
MVP

28-01-2019

Hi,

Use xtk function AddHours() within an update activity.

Thanks,

-Jon

Answers (3)

Answers (3)

Avatar

Avatar
Springboard
MVP
Venu_Reddy
MVP

Likes

163 likes

Total Posts

171 posts

Correct reply

53 solutions
Top badges earned
Springboard
Establish
Give Back 25
Give Back 10
Give Back 5
View profile

Avatar
Springboard
MVP
Venu_Reddy
MVP

Likes

163 likes

Total Posts

171 posts

Correct reply

53 solutions
Top badges earned
Springboard
Establish
Give Back 25
Give Back 10
Give Back 5
View profile
Venu_Reddy
MVP

01-02-2019

Hi Marcin,

Suppose we can assume that your current timezone is 'A' and planning to convert to 'B',

Make changes in update activity like:

1.) If A is more time compare to B then

SubHours($datetime(vars/XYZ) , C)

1.) If B is more time compare to A then

AddHours($datetime(vars/XYZ) , C)

Here XYZ is the variable name you are using in the workflow and 'C' is the difference between the two time zones.

Hope you are clear with the above scenario.

Regards,

Venu

Avatar

Avatar
Springboard
MVP
Venu_Reddy
MVP

Likes

163 likes

Total Posts

171 posts

Correct reply

53 solutions
Top badges earned
Springboard
Establish
Give Back 25
Give Back 10
Give Back 5
View profile

Avatar
Springboard
MVP
Venu_Reddy
MVP

Likes

163 likes

Total Posts

171 posts

Correct reply

53 solutions
Top badges earned
Springboard
Establish
Give Back 25
Give Back 10
Give Back 5
View profile
Venu_Reddy
MVP

28-01-2019

Hi Marcin,

Actually, the daylight time saving is correctly managed by both servers (applicative and MTA) and database layer and its own server, and so by Adobe Campaign software, if the data type for datetime elements have been set to multi-zone.

Hence, please check that carefully in the Adobe Campaigns options.

But sometimes it can happen that the problem arises if there are not synchronised time to worldwide clock (NTP protocol) or bad time settings).

If the Adobe Campaign options have not been set to multi-zone, please ask for technical operation to update/migrate your DB, it would far more convenient for you. But in the meanwhile, I don't see other solutions than manual and local calculations

Note: Use expression (List of functions) like date +/- the time zone which you are expecting (Use 'Taken into account if' for better results).

Regards,

Venu

Avatar

Avatar
Give Back 5
Level 3
marcinkacz
Level 3

Likes

19 likes

Total Posts

46 posts

Correct reply

2 solutions
Top badges earned
Give Back 5
Give Back 3
Give Back
Ignite 1
Validate 10
View profile

Avatar
Give Back 5
Level 3
marcinkacz
Level 3

Likes

19 likes

Total Posts

46 posts

Correct reply

2 solutions
Top badges earned
Give Back 5
Give Back 3
Give Back
Ignite 1
Validate 10
View profile
marcinkacz
Level 3

31-01-2019

Thank you All for your tips. FYI I decided to use simple approach: choose proper time zone in workflow's properties. With this settings time is converted into desired time zone directly in Data extraction (file) activity without any additional code (so I'm not saving it in temp table).

Venu, I have additional questions to your post:

- What is this option name?

- How properly use  'Taken into account if' ?

Thank you,

Marcin