Adjusting a Charachter Date/Time field to add or subtract 1 hour

Gerard Usher

Forums

I have imported a Date/Time field from an outside source and need to add 1 hour to the time to compare CST against EST. The field is formatted as Character/19 and contains YYYY-MM-DD HH:MM:SS. 
I'm sure there must be a simple solution to add or subtract hours from a Date/Time to adjust for different timezones but I can't find it.  Can you help?
 
 

Steven Luciani Mon, 10/29/2018 - 07:56

Hello Gerard,

If you want a TIME_EST and TIME CST field I will show you the two equations below. If you want to just break the time portion out of your imported filed and display it as time in CST I will show you that nested equation as well.

To create the time EST field, create a virtual time field called TIME_EST and use this equation (I'm assuming your imported field name is DATE_TIME)

@ctot(@right(DATE_TIME,8),"HH:MM:SS")

To create the TIME_CST field, use this equation.

@ntot(@Tton(TIME_EST)+3600)

@tton changes the time to a number in seconds so you add 3600 seconds or 1 hour to the time as a number the @ntot converts it back to a time field.

I'm a fan of only creating one field so here is the equation to do it in one step assuming you don't need a TIME_EST field

@ntot(@Tton(@ctot(@right(DATE_TIME,8),"HH:MM:SS")+3600))

Cheers,

Steven

 

Gerard Usher Mon, 10/29/2018 - 16:25

Hi Steven.  Thanks for your input.  Unfortunately, this does not solve my issue because some of the data I need to convert contains times that run throughout the day, including, between 11:pm and midnight CST. The data which I need to match it to shows the date/time in EST. Whichever formula I use will not get around the date change e.g. if I have a date/time of 09/01/2018 23:00:12, the scripts that you have listed just create a new time of 24:00:12. The date remains the same.  What I need to do is have 09/01/2018 23:00:12 converted to 09/02/2018 00:00:12.  So far, I have not found any documentation which addresses this issue.

Steven Luciani Tue, 10/30/2018 - 08:13

Hi Gerald,

I've fixed the 23 to 00 problem and the date roll over problem.

I'm not sure how you want it displayed in your data so I'll give you file equations based on my imported field name of DATE_TIME_EST which is Character 19 YYYY_MM_DD HH:MM:SS

DATE_EST: @ctod(@left(DATE_TIME_EST,10),"yyyy-mm-dd")

TIME_EST: @ctot(@right(DATE_TIME_est,8),"HH:MM:SS")

TIME_CST:  @if(@hours(TIME_est)=23,@ctot(@replace(@ttoc(TIME_est),"23","00"),"HH:MM:SS"), @ntot(@Tton(time_est)+3600))

You have to create time CST before DATE CST

DATE_CST: @if(@hours(TIME_CST)=0,@DaysToD(@dtodays(date_est)+1),date_est)

DATE_TIME_CST: @Dtoc(DATE_CST,"yyyy-mm-dd") + " " + @ttoc(TIME_CST)

This equation concatenates the CST dates and times back into one character field.

Hope this helps.

Steven

Gerard Usher Tue, 10/30/2018 - 10:44

Hi Steve,
 
That's exactly what I needed.  The formula that you published actually adds 1 hour, so it converts CST to EST, which is what I needed to do.  Thanks again for taking the time to fix the 23:00 to 00:00 time issue and the date roll-over issue!

Steven Luciani Tue, 10/30/2018 - 11:08

Hi Gerald,

Glad I could help. I just realized I got my CST and EST backwards. This will work for all time zones you just have to adjust the 3600 and the sign for larger time zone jumps and direction of travel. You would also have to adjust the date equation based on travel direction and time zone jumps.

I'm sure Brian Element could come up with a custom function or IDEAScript with a user input box where you choose your timezone jumb and direction.