Page 1 of 1
Adjusting date for UTC offset
Posted: Tue Mar 02, 2010 2:54 pm
by hobocamp
I can't seem to find an answer to this in the forums, but I'm sure someone must have run into this issue before.
In an Enterprise job, I need to subtract a given number (the UTC/GMT offset) from a timestamp field, and return the new timestamp.
It doesn't appear to me that any of the built-in functions would do this, so does anyone have any suggestions on how to accomplish it?
Thanks for your help.
Tom
Posted: Tue Mar 02, 2010 3:18 pm
by ray.wurlod
Generally (world-wide) or only for one particular time zone? Do they have daylight saving time to be taken into consideration?
Posted: Tue Mar 02, 2010 4:01 pm
by hobocamp
Ray -
It's just for one time zone. In fact the value is provided in the source data that I receive, so I simply have to take that value and subtract that number of hours from the timestamp.
Posted: Tue Mar 02, 2010 5:26 pm
by ray.wurlod
Probably a function like TimestampFromSecondsSince() using 3600 * hours as the offset.
Posted: Wed Mar 03, 2010 10:36 am
by hobocamp
Thanks Ray! I tried that function and it provided what I needed. It was a much more elegant solution than what I was coming up with.
Thanks again.
Tom
Posted: Fri Apr 02, 2010 3:10 pm
by anu123
ray.wurlod wrote:Probably a function like TimestampFromSecondsSince() using 3600 * hours as the offset. ...
Ray-
How to pass the number of hours to offset since it will change with day light saving.
Posted: Fri Apr 02, 2010 3:42 pm
by ray.wurlod
Since this is a time- (date-) variant quantity, I'd recommend calculating the hours separately from the job and passing it as a job parameter value. Perhaps you could interrogate the TZ environment variable.
Even better would be to have a daylight saving flag in a time dimension table, or in a related table. A great example for a range lookup.