How can i automatically change the time zone in our Canvas Data extracts?

[cathy_himmelman]

I am working with our Canvas Data Portal extract files. Let me say that I am very new at this and have no coding background. I wish to eventually use the data with Access so that I can query, run reports, etc. We are in the Eastern Time Zone; however, all data from Canvas extracts are from Canvas's time zone. I know that I can take the data which extracts as all text into Excel and run the formula =cell-(5/24) and format the cells to make these changes. It just seems that there should be an easier way to do this. I mean you are talking about over 80 files that contain the wrong time zone. I have the data files downloading and unpacking every day. I just need help with this time zone issue. Any thoughts out there? Your input will be greatly appreciated. Thanks in advance.

[dwillmore · Accepted solution]

Because these times are from the server logs I don't believe you can change the times before you gather the data.   There are different ways of handling this after you collect data and you mentioned one.

[cathy_himmelman]

Thanks for your response, David. What other ways might I approach this? Java script, code...?? As I mentioned, I do not code; however, can usually figure out what I need to do. Just trying to simplify this issue. Thanks.

[dwillmore]

The simplest is probably to add 2 to the time field.  The time and date field are one I believe so you would need to split the column.

A program or script would work, but I lack those skills at the moment.   

I am normally the person that folks go to for log questions and I just do the monkey thing and change the time in my head when I report my findings.  If I were to keep full reports I would use Excel as you are using it.

[amcdona]

Here is how I query from Oracle (where my files get staged):

SELECT to_char(from_tz(timestamp,'UTC') at time zone 'US/Eastern', 'YYYY-MM-DD HH24:MI:ss') AS timestamp

FROM REQUESTS

[a1222252]

That's how we do it too. If you cast the result you can get it in the appropriate datatype:

select cast(from_tz(timestamp,'UTC') at time zone 'Australia/Adelaide' as timestamp) from fct_lm_requests...

You can also use this to replace the timestamp_dat with the correct day in local time:

select cast(from_tz(timestamp,'UTC') at time zone 'Australia/Adelaide' as date) from fct_lm_requests...

We have partitioned the fct_lm_requests table on timestamp_month for ease of maintenance and it contains the full requests history.

[sam_mcknight]

Andrew or Stuart - Any suggestions on how to convert it to the user's timezone that is in their user_dim record? The timezone string stored in Canvas doesn't seem to match up with what Oracle expects in the conversion function, but maybe I'm missing something.

What we want to look at is not the UTC time but the time the user interacted at their local time. We have users across the US and knowing their local time will help analyze usage patterns.

[James]

Here are the Ruby on Rails TimeZone mappings: ActiveSupport::TimeZone 

Example "Eastern Time (US & Canada)" => "America/New_York"

The key is the Rails TimeZone name and the value is TZInfo identifier.

[a1222252]

Hi Samuel,

Looking at the user_dim.time_zone values that are populated in our database, (most are null), the time zone is the name of the city, which is not a normally recognized time zone.

I don't know how this gets populated on user creation or what options there are for changing the values there.

You could address this in the database by translating the city name to an Oracle-recognised timezone, (ie. one which exists in V$TIMEZONE_NAMES). This usually, (but not always), means prepending the city name with the continent.

For example, Adelaide becomes Australia/Adelaide etc.

You probably also want to check that your timezone file is up-to-date for the zones you want to use. This keeps changes to daylight saving changeover dates current. Check the version in V$TIMEZONE_FILE. The latest is version 26. Oracle note 412160.1 discusses this.

Regards,

Stuart.

[James]

 @cathy_himmelman  

The timestamps are not from Canvas' time zone, they're UTC (Coordinated Universal Time) and are therefore not subject to whims like daylight saving time that make it difficult to handle. To illustrate that whimsical nature of dealing with times, you say to subtract 5/24, but that's for EST, but in March, you probably switched over to EDT and so now you're subtracting 4/24.

Microsoft offers this page that tells how to convert UTC to local time for MS Access. It makes it look simpler than it is, though.

However, you can pull the same Excel Trick within Access. You can create a formula in Access.

Let's say that I currently have a field called utctime and I want to change it to localtime.

Click on the Click to Add and choose Calculated Field and then Date/Time.

For the formula, enter [name_of_utc_time_field] - 5/24

Then rename the created field to reflect what you want to call it. Here I renamed it localtime.

You would need to repeat that process for each field that you needed to change the timestamps for.

More robust handling would be to use the IIF function to check to see if the date was before you switched to EDT and subtract 5/24 otherwise subtract 4/24.

IIf([utctime]<DateSerial(2017,3,12),[utctime]-5/24,[utctime]-4/24)‍‍

Of course, this gets complicated when you have more than one time change involved.

For that, and a whole bunch of other reasons, I would suggest leaving the timestamps in UTC time rather than doing a wholesale changing of every date. If you need to reflect local time for some reporting, then I would use software that will convert them to local time for you. I haven't used MS Access much, but I suspect it's not designed for the size of files you're going to get from Canvas Data. Microsoft wants people to use SQL Server (more power = more money). A little research into this timezone question suggests Access doesn't handle timezone information directly, but relies on the timezone in Windows. That might work for a small shop, but when you have users in different time zones, UTC is the preferred way to go. That does make it difficult to figure out how many people have logged in between 8 and 9 am, when 8 and 9 am are changing twice a year.