How to convert GMT to CST from Audit database? 2

[cmmrfrds]

I am trying to convert the GMT field in the Audit database to CST. CST is -6 hours from GMT unless it is day light savings time then it is -5 hours. How do I determine whether to use -5 or -6 in my conversion for a given date.

 

[synapsevampire]

There are a few approaches here, and the optimal solution would be based on your version of software, and the database.

There are UFL's.

Write a formula.

Do it on the database.

There are lots of posts here as well, search for GMT, or post specifics.

-k

 

[cmmrfrds]

The posts I found did not handle day light savings time.

I was hoping somebody was familiar with the CE10 auditing database and had already solved the problem with converting the timestamp field the is in GMT to another timezone that deals with day light savings time.

The database is the Audit database for CE10.

The table is CMS_AUDITTABLE

Field name is TimeStamp and is a string data type. This is stored in GMT.

I can do this in a formula
// Convert string to a date
dateadd("h",-5,dtstodatetime({AuditElement.Timestamp}))

but this assumes day light savings time. I need to handle the conversion for both CST and CDT depending on the date value.

Has anybody done this is a formula?

 

[Turkbear]

Hi,
You just need to add a date check before the main formula:

If Currentdate in date(when dst starts) to date(whenitends)
then
dateadd("h",-5,dtstodatetime({AuditElement.Timestamp}))
else
dateadd("h",-6,dtstodatetime({AuditElement.Timestamp}))

Probably better formulas but this should work..

 

[cmmrfrds]

Thanks for the input. I just hardcoded the next few years of DST in a formula, which is good enough for now.

 

[kcgamz]

I actually just recently created a custom function in CR9 because I couldn't find a tidy solution to exactly the same problem. Here it is for what it's worth.

Function (DateTimeVar gmtDateTime)
//This function converts any GMT date-time to a Local date-time after
//checking whether the date is during Daylight Saving Time or not.

NumberVar i;
DateVar searchDate;
DateVar beginDST;
DateVar endDST;
NumberVar timeDiff;
DateTimeVar localDateTime;

//Find the DST begin date (which is always the first Sunday in April)
//for the year of the "gmtDateTime" argument.
For i := 1 To 30 Do
(
searchDate := Date(Year(gmtDateTime), 04, i);
If (DayOfWeek(searchDate) = 1) then Exit For
);
beginDST := searchDate;

//Find the DST end date (which is always the last Sunday in October)
//for the year of the "gmtDateTime" argument.
For i := 31 To 1 Step -1 Do
(
searchDate := Date(Year(gmtDateTime), 10, i);
If (DayOfWeek (searchDate) = 1) then Exit For
);
endDST := searchDate;

//The difference between GMT and U.S. Central time zone is 5 hours during
//DST or 6 hours if not.
If gmtDateTime in beginDST to endDST then timeDiff := 5 else timeDiff := 6;

//Now convert GMT to Local
localDateTime := DateAdd ("h", -timeDiff, gmtDateTime)

Jerry

 

[cmmrfrds]

kcgamz, that is perfect. Thank you very much.