Can See Assignment Due Date in API But Not Canvas Data

[brett_sheeran]

Hi,

I'm finding some assignments show a due date when I query the API, but *not* when I query Canvas Data.

For example, if I query assignment 29092 through the API like so:
https://stpetersgirls.instructure.com/api/v1/courses/5148/assignments/29092?access_token=<snip/>

The API returns:
due_at: 2018-06-18T14:29:00Z

Whereas, if I run this SQL:
select id, title, due_at from assignment_dim a where canvas_id = 29092;

The DB returns:
due_at: 0000-00-00 00:00:00

Incidentally, have tried downloading the data several times. I also ran the following query to see if there were any overrides, but couldn't find anything.
select * from assignment_override_dim where assignment_id = 32760000000029092;

Any suggestions?

Thanks

Regards Brett Sh

[stimme]

Hi  @brett_sheeran  - 

There are a several possible causes for a discrepancy between the API's assignment due_at value and Canvas Data's. First, there's probably nothing is wrong.  The API returns live information from the Canvas application, whereas the database is a snapshot taken in the past.  So, one would expect a discrepancy if the due_at value was added to the assignment within the last couple of days.

Second, something might be off with the assignment_dim.due_at column definition.  If that were the case, even though the correct due_at value would be in the flat file, it wouldn't be in the database after load.  Two ways to see if it's something like this are:

1. Download the assignment_dim gzip flat files from the Canvas Data Portal. Search them for the assignment record quickly from a *nix command line with zgrep. For example, `zgrep 32760000000029092 *.gz`. Then check to see whether value in the due_at position is the date or NULL. If NULL, then the column in the database must use '0000-00-00 00:00:00' as its default and not allow null values.  In this case, nothing is wrong; the snapshot will catch up.
2. Query the assignment_dim table to make sure that values other than '0000-00-00 00:00:00' are in the due_at field. For example, `SELECT COUNT(DISTINCT due_at) FROM assignment_dim;` If there's just 1 distinct value, then due dates aren't loading correctly.

I hope this helps,

Sam Timme