cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Community Member

Is there a "data as of" date in Canvas Data?

Jump to solution

Hi, I'm looking for a "data extract date" or "data as of date" so that we know when the data was pulled into Redshift.  I didn't see a status date or table anywhere in the documentation, but maybe I missed it?  We could retrieve the max(timestamp) from one of the fact tables, but is that the best/only way?

1 Solution

Accepted Solutions
Highlighted
Community Coach
Community Coach

Thanks for your question, Ruby Nugent​. At this time the best we can say is that the date associated with the latest dump is the data extract date.

View solution in original post

12 Replies
Highlighted
Community Coach
Community Coach

Thanks for your question, Ruby Nugent​. At this time the best we can say is that the date associated with the latest dump is the data extract date.

View solution in original post

Highlighted
Community Member

You mean the date from the files?  Is there anything within the Redshift DW?

Community Member

Looks like the "best" date from within the Redshift instance would be max(timestamp) from requests - which is a day earlier than the date on the files.  We plan to use that as the data extract date for communication to usrs.

0 Kudos
Highlighted

I sometimes see a discrepancy where the max date from requests is days prior to a max date for a discussion post.  I assume this to be related to the various issues handling what I understand to be very large requests table.

So for this I am using MAX date of the discussion_entry_dim.created_at field.  I can do so as all of our courses use discussions.

Highlighted
Adventurer

rubyn@umich.edu

As fernerj@erau.edu  says, you should look at the MAX date of a created_at or updated_at field.  submission_dim is a good one for us because it's our largest table outside of requests. 

I import every day and I have an extra field in our DB for data_timestamp that puts the timestamp of when the data import process was started.  That way we at least know how old OUR imported data is. 

Highlighted

You are awesome, Joni.

ps.  chances are many of us have this but just in case that isn't so for extra credit I am sharing a few lines I use to convert the UTC timestamp to EDT/EST.  Cheers ~!

---- 

if date([Timestamp]) > #13-03-2016# and date([Timestamp]) < #06-11-2016# then DATEADD('hour',-4,[Timestamp])
elseif date([Timestamp]) > #12-03-2017# and date([Timestamp]) < #05-11-2017# then DATEADD('hour',-4,[Timestamp])
elseif date([Timestamp]) > #11-03-2018# and date([Timestamp]) < #04-11-2018# then DATEADD('hour',-4,[Timestamp])
elseif date([Timestamp]) > #10-03-2019# and date([Timestamp]) < #03-11-2019# then DATEADD('hour',-4,[Timestamp])
elseif date([Timestamp]) > #08-03-2020# and date([Timestamp]) < #01-11-2020# then DATEADD('hour',-4,[Timestamp])
elseif date([Timestamp]) > #14-03-2021# and date([Timestamp]) < #07-11-2021# then DATEADD('hour',-4,[Timestamp])
elseif date([Timestamp]) > #13-03-2022# and date([Timestamp]) < #06-11-2022# then DATEADD('hour',-4,[Timestamp])
elseif date([Timestamp]) > #12-03-2023# and date([Timestamp]) < #05-11-2023# then DATEADD('hour',-4,[Timestamp])
elseif date([Timestamp]) > #10-03-2024# and date([Timestamp]) < #03-11-2024# then DATEADD('hour',-4,[Timestamp])
elseif date([Timestamp]) > #09-03-2025# and date([Timestamp]) < #02-11-2025# then DATEADD('hour',-4,[Timestamp])
else DATEADD('hour',-5,[Timestamp]) end

Highlighted

fernerj@erau.edu   do you store in the db in UTC or local?  right now I'm storing in UTC and just convert on query. 

0 Kudos
Highlighted

^^^ Exactly the same here -->  convert in the reports.

0 Kudos
Highlighted

All,

Two issues here. Firstly, requests data is always delayed by more than the rest of the data. Clearly, the latest requests data can be found by max(timestamp). For the rest of the data I've found that max(last_request_at) in the pseudonym dim to be the best indicator. For the data this morning, this is what I see in UTC:

requests:               2019-11-02 23:59:59.973

pseudonym_dim:  2019-11-04 00:38:21.533

Secondly, not sure which platform you are using, but Oracle has built-in functionality for timezone conversion including automatically handling daylight saving time changes. We extract all data from the text files into raw tables using VARCHAR2 and CLOB data types, then transform the data to convert '\N' strings to nulls and date / timestamp data to appropriate datatypes in local time in a second set of tables. For example, here's how we convert the requests timestamp column to give UTC and local time columns in the transformed table:

SELECT

TO_TIMESTAMP(DECODE(lmrr.timestamp, '\N', NULL, lmrr.timestamp), 'YYYY-MM-DD HH24:MI:SS.FF') AS timestamp_utc,
CAST(FROM_TZ(TO_TIMESTAMP(DECODE(lmrr.timestamp, '\N', NULL, lmrr.timestamp), 'YYYY-MM-DD HH24:MI:SS.FF'), 'UTC') AT TIME ZONE 'Australia/Adelaide' AS TIMESTAMP) AS timestamp

~

~

FROM dwstglm.lm_requests_raw lmrr;

Regards,

Stuart.

0 Kudos