-1

On MariaDB 10, I'm trying to work with dates, particularly whether the current date is within 7 days of a date stored in the database. A longtext column holds an Epoch datetime, which I convert into a date-only using FROM_UNIXTIME(t2.meta_value, '%d/%m/%Y'), and it returns nicely like 11/03/2019 (UK time formatting). But if I try to use it in datediff using a second parameter like now(), datediff returns NULL - and PHPMyAdmin shows a #1292 incorrect datetime value. I've additionally tried adding date_format and str_to_date without any remedy, and plenty of reading through Stackoverflow and internet articles.

FROM_UNIXTIME(column, '%d/%m/%Y') returns 11/03/2019

datediff(date_format(now(), '%d/%m/%Y'),FROM_UNIXTIME(column, '%d/%m/%Y'))

returns NULL, whether I use date_format or not.

I've also tried moving the code to the where clause, but this returns a #1292 error as well : WHERE FROM_UNIXTIME(t2.meta_value, '%d/%m/%Y') > curdate()

  • 1
    The main problem is you are using a TEXT field to store a date. Stop doing that and you'll find lots of aspect about date handing so much easier. In your mess of functions keep track of what is returned i.e date_format returns text so why is that being passed to datediff. Don't try to get it into UK date format for comparison, use that for results only if you really must. – danblack Feb 28 at 11:23
  • @danblack - sadly, it's not my schema so I can't change the text field. – Peter Schofield Feb 28 at 11:40
0

Using CAST to DATETIME got the database engine to recognise that it was a date, and then it was simpler from there.

CAST(FROM_UNIXTIME(column) as DATETIME)

Thanks to danblack

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.