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!