Closed Bug 466242 Opened 16 years ago Closed 15 years ago

solve the database date confusion

Categories

(Socorro :: General, task)

x86
Linux
task
Not set
normal

Tracking

(Not tracked)

RESOLVED FIXED

People

(Reporter: lars, Assigned: lars)

References

Details

The reports table has four (5?) date columns in it.  The jobs table has three date columns.  Do they all need to be there?  Are they all used somewhere?  What set of dates would be optimal for us to provide the statistics that we need?  

Since I am mucking about in the database for partitioning, this is an ideal time to make some changes.  

Summary of the dates in the database now:

Jobs Table:
queueddatetime: (ts w/o tz) the time when this job hit the jobs table
starteddatetime: (ts w/o tz) the time when a processor got the job
completedatetime: (ts w/o tz) the time when the processor finished the job

Reports Table:
'date': (ts w tz) the time the crash happened as reported by the client.  I'd like to change the name to 'crash_date'

'date_processed': (ts w/o tz) the time that this record appeared in the database as reported by the database.  Neither monitor nor processor uses this value for anything.

'starteddatetime': (ts w/o tz) copied from the jobs table.  Considering changing the name to 'started_datetime' to match other column names

'completeddatetime':  (ts w/o tz) copied from the jobs table.  Considering changing the name to 'completed_datetime' to match other column names

There is a stealth date also in the reports table.  The 'uuid' column has the date that the collector received the job embedded in it.  I had been using the 'date' column as the partitioning constraint, however, I realize now that this is a mistake because it's reported by the client.  We cannot trust a date reported by the client.  I will be changing to using the 'uuid' date to prevent mismatches.  This will probably result in another date column added to the reports table called 'received_date'

What do you think?
IIRC, date_processed was originally added to use for partitioning exactly because the 'date' field is client-provided.
the dates have all been sorted out.  Discrepancies between dates with and without time zones have been fixed.  The changes have been deployed to production with the partitioning changes.
Status: NEW → RESOLVED
Closed: 15 years ago
Resolution: --- → FIXED
Depends on: 479256
Component: Socorro → General
Product: Webtools → Socorro
You need to log in before you can comment on or make changes to this bug.