Adjusting date for UTC offset

hobocamp · Post by **hobocamp** » Tue Mar 02, 2010 2:54 pm

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

ray.wurlod · Post by **ray.wurlod** » Tue Mar 02, 2010 3:18 pm

Generally (world-wide) or only for one particular time zone? Do they have daylight saving time to be taken into consideration?

hobocamp · Post by **hobocamp** » Tue Mar 02, 2010 4:01 pm

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.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 02, 2010 5:26 pm

Probably a function like TimestampFromSecondsSince() using 3600 * hours as the offset.

hobocamp · Post by **hobocamp** » Wed Mar 03, 2010 10:36 am

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

anu123 · Post by **anu123** » Fri Apr 02, 2010 3:10 pm

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.

ray.wurlod · Post by **ray.wurlod** » Fri Apr 02, 2010 3:42 pm

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.