Getting different time from Database in neolane.

Level 2

Whatever hour i am getting, i need to subtract -1 from the hr i.e. if date along with time 01-09-2017 02:23:23 i need to show 01-09-2017 01:23:23. Can you please let me out in the same. how should i proceed with the same. Using gethour function. will it make sense to do gethour()-1;

Jean-Serge_Biro

Level 10

Hi nidhiv,

Please may you share more details?
Your Javascript code: do you use GetDate, or date through queryDef, etc.

The data type of the date field in your XML schema and in your DB
The timezone of your server is it the same than your DB server.

Date time management is tricky, I must admit.

Is there someone else that can give some ideas/advices?

Regards
J-Serge

Level 2

My approach to fetch the date using new Date in workflow using targetData. But i was unable to determine why it was not picking up the time although date was coming perfectly fine. It was showing 2 everytime along with date. So i tried using queryDef, then i get to know it was showing timezone instead of time. As in database, it was date type column as well as in neolane schema. When i changed , its date type to datetime. It was working fine .

I would like to know - During day light saving, will dates and time impacted with the same? If so, then how we should handle the time for different countries having vast difference in time i.e. US , Russia. Can please suggest on the same.

Regards,

Nidhi

Jean-Serge_BiroAccepted solution

Level 10

Hi Nidhi,

Yes, it is really tricky, and all people struggle with that kind of issue, not only in Adobe Campaign area, but all IT areas.

In AC Javascript activity, please note that if you user new date() you get the applicative server hour/timezone for workflows but for webApp the webServer is not usually the same as applicative server (often it is located on the MTA server). And when you use GetDate() the hour/timezone returned comes from the database server itself (the database instance actually, not the machine).
So you can get different hours/time depending where are your applicative server, your web servers/MTA, and your database instance/server.
You should compare the datetime of the 3 servers (2 AC servers and the DB instance) in your case.

Then I recommend using GetDate (and other functions) because it is the same function that you can use in queryDef, Query activity.

If you have a doubt -or want to better understand) on hour/datetime functions implementations, in Query activity you can display the SQL command generated.
And you can have a look also to the Adobe Campaign generated SQL functions as SQL User functions depending on your DB engine. For instance for SQL Server, Adobe Campaign transforms the DateOnly to this User Function:

So by looking at the underlying SQL User function, you can better understand what you get, when you develop with DB oriented datetime functions (instead of Javascript ones).

Have a nice week-end.
Regards
J-Serge

Level 2

I would like to ask 1 doubt regarding the time in day light saving. In case of Workflows, we need to make substraction or addition in the time used to fetch from database due to time variation of database as well as neolane. Also each market having different time zone. Can you please let me know the work around to resolve the same like we need not do handle day light saving time as well as normal time separately by doing addition/subtraction . Please suggest optimize solution to it

Thanks Much in Advance,

Nidhi

Jean-Serge_Biro

Level 10

Hi Nidhi,

Please may you tell us which elements time format you have in your schema? And please check also the underlying SQL data type.

And also all your servers time and timezone settings.

Actually, normally, 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

Regards
J-Serge

Level 2