I have a database of articles that has the date the article was published as a UNIX timestamp that is saved as a varchar value. For example, one article's datePublished value is 1667865600000 (varchar).
I'm querying the database to return articles with a certain keyword in it from the last 90 days. But I don't think my current approach is successfully querying the database for the 'datePublished' value, since my date published value is a varchar and not a date value. However, I can't verify this because it's not letting me know if ('datePublished' > DATE_SUB(now(), INTERVAL 90 DAY))
is actually doing anything or not. I do think it is ordering by datePublished DESC
successfully, though.
How do I properly query the database's datePublished value as a varchar UNIX timestamp?
Here is my query
SELECT *
FROM news
WHERE
(MATCH(snippet) AGAINST("example" IN BOOLEAN MODE))
AND ('datePublished' > DATE_SUB(now(), INTERVAL 90 DAY))
ORDER BY datePublished DESC LIMIT 100
CodePudding user response:
You can filter directly against the Unix timestamp:
datePublished > unixtimestamp(now() - interval 90 day)
That should be good enough for MySQL to implicitly cast the string to a number. But if that's not happening, then we can force it like so:
datePublished 0 > unixtimestamp(now() - interval 90 day)
Note that I fixed your original query, where you had the column name surrounded with single quotes ; this trivial typo causes the literal column name to be used as fixed value for the whole column...