Search code examples
mysqlsqlwhere-clausedate-arithmetic

querying varchar date timestamp and ordering by date descending

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
Solution

  • You can filter directly against the Unix timestamp:

    datePublished > unix_timestamp(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 > unix_timestamp(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...