Difficult Dates

I had an interesting thought about dates which led to me learning something and then feeling a bit stupid.

The thing to bear in mind here is that I’m thinking a lot about how this tool could accumulate a LOT of data, and how I can make reporting fast.  Perhaps I should jus trust MySQL, but I don’t.

Laravel, by default, adds created_at and updated_at columns to tables in the database. These are TIMESTAMP typed values and always appear in “YYYY-MM-DD HH:MM:SS” format.  I figured I’d use the created_at attribute for the timestamp of the pageview.

But then I was thinking about reporting, and how if, say, I wanted to show views from the last 7 days, this might be inefficient. The reasoning for this was that timestamps appear to be strings like “YYYY-MM-DD HH:MM:SS” as this is how you store them and this is how they are retrieved in queries. In my head I know that a string comparison is going to be slow so querying based on comparisons of this column felt like they could be bad.

So I actually got all the way to adding the current time as an integer timestamp in a separate attribute before stopping and thinking: how does MySQL store TIMESTAMP type values internally. And, it turns out, they’re stored as integers anyway.

I can use Carbon to easily set up the query parameters when querying this and all the comparisons are, internally, done numerically. So that’s cool.

Time to roll back!