LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: awibowo

Hi,

I am trying to create a timestamp from a string

for example:
input: 20100430
formula: timestamp(date('input', "CCYYMMDD")
expected result: 2010-04-30 00:00:00 (datetime type)

However, I got 2010-04-29 23:00:00

Oddly, if I used the example from the help menu
timestamp(date(2003, 3, 17)), I got 2003-03-17 00:00:00 as expected

If I changed the input to
timestamp(date(2003, 4, 17)), I got 2003-04-16 23:00:00

I tried with various days in March 2003, the results were as expected but as soon as I changed the year or month, it substracted 1 hour from the expected value. It seems like March 2003 is somewhat a "special" case

As temporary solution, I have the following
timestamp(date('Worked Date', "CCYYMMDD"), time('Job Start Time Actual',"HHMM")).dateTimeAdjust(1, "hours")

I have checked the results randomly, they seem to be correct, but I am not sure if any of them "incorrectly" add the 1-hour.

I am wondering if anyone has encountered the same issue?

Thanks

a.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

awibowo,

Good Day.
A quick check:
You note that you do NOT see the issue when using the example data of March 17, 2003.
Do you see similar behavior when using March 17, 2010? What kind of changes to the year and month did you make? Does Feb, 2003 also work okay, but April 2003 does not?

The timestamp function is sensitive to the local timezone. Viewing it in BRE (via BRDviewer) will make adjustments based on your system settings. So timestamps generated in one timezone may be viewed differently on your local machine. This also can include changes due to Daylight Savings depending on your location. Often times when people see hour-subtraction or hour-addition discrepencies when using timestamp (datetime types) it can be related to this.

Regards
Timon Koufopoulos
MDA Support.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: awibowo

Hi Timon,

I tried timestamp(date(20XX, 3, 17)) with various years

XX:
00-06 -> 20XX-03-17 00:00:00
07-10 -> 20XX-03-16 23:00:00

Because of the sensitivity of the function, is there a better way to create a datetime type from a string date and append the (00:00:00) time ?

Thanks for your help

Andy.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: awibowo

Also I found out my first temp solution doesn't quite work as I want it to.

ts=timestamp(date('Worked Date', "CCYYMMDD"), time('Start Time',"HHMM"))#.dateTimeAdjust(1, "hours")

input:
Worked Date(string): 20100418
Start Time(string): 0800

output:
ts = 2010-04-18 08:00:00 <- here if I take out the dateTimeAdjust it will show 07:00

Now if I want to work with the time/date of the ts
dt_StartTime = date(ts)
tm_StartTime = time(ts)

output:
dt_StartTime(date) = 2010-04-18
tm_StartTime(time) = 09:00:00 <- it adds the 1 hour here.

If I take out the dateTimeAdjust, I get the correct date/time if they're separated and back the the problem of -1 hour if they're together as datetime

a.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: matt

Preface: I am assuming you are using a BRAIN server on a Solaris or Linux box.

The problem you are describing is caused by Windows incorrectly handling historical daylight savings time (DST) rules, and the DST rules that changed in 2007 for North America. Basically, Windows does not keep track of historical daylight savings changes but Solaris and/or Linux does.

A little background:

BRAIN stores timestamps as milliseconds from UTC. This allows for a timestamp to represent an exact moment in time independent of the local timezone. Date and Times are simply the representation of a date or time on a calendar. When converting to a date or time from a timestamp, BRAIN uses the server's timezone and daylight savings time rules to calculate the offset from UTC. When BRD Viewer displays a timestamp, it also needs to do a conversion to a representable time, but it will use the workstation's timezone and daylight savings time rules, which happens to be Windows'.

The problem occurs because of the following (assuming EDT here):

Example: 2006-03-17 (not DST in 2006, DST in 2007 or after)
1. Server converts 2006-03-17 00:00:00 to 1142553600000 ms UTC using the 2006 DST rule
2. BRD Viewer converts 1142553600000 to 2006-03-17 01:00:00 using the current DST rule due to Windows.

This explains the behavior you were seeing across the 2007 boundary using the 20XX-03-17 date. The good thing about this bug is that it is simply a display issue for BRD Viewer, the date is actually correct in the data. You can check this by pulling apart the datetime on the server i.e. date(<timestamp>).

Now this is not exactly what you are seeing, but from your first post, I believe that the server you are using is not in the same timezone. The timezone the BrainScript is run under is the timezone of the server, not your workstation. The timezone of the BRD Viewer is the of the workstation.

More information on date and time manipulation can be found in the BRAIN Release Notes in the 4.1.4 section.

Could you please find out the timezone setting for the server you are using and the timezone of your workstation?


Matt

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Leaner

Thanks for this post Matt. I just ran into this problem today and this confirms my fears.

Data is coming from our MSSQL server to our Linux Lavastorm Server back to a Windows 7 BRE Client.

Unfortunately, this bug/error/oversight/whateverYouWannaCallIt has cost us several thousand pounds.

Any fix available?

Thanks.