CODEWITHSUNDEEP
mysqlunix-timestamp
Matteo
Matteo

Reputation: 1219

UNIX_TIMESTAMP() comparison

Hello everyone I've a problem with UNIX_TIMESTAMP(): when I'm using it to check it against another UNIX_TIMESTAMP() I never get the result I expect.

Here a small query as example:

SELECT
  UNIX_TIMESTAMP(STR_TO_DATE(mydate, '%Y%m%d')) as Date1,
  UNIX_TIMESTAMP(STR_TO_DATE(mydate2, '%Y%m%d')) as Date2
FROM
  my_table
WHERE
  (UNIX_TIMESTAMP() BETWEEN Date1 and Date2 )

Where Date1 as 1387062000 as value and Date2 is 1389740400, and unless we are after Jan 2014 the result from UNIX_TIMESTAMP() is between the 2 values.

Still it gives me no results when I should be getting at least 1 row from the table; I've tried to format the date before feeding it to UNIX_TIMESTAMP() but I've failed in every try.

Probably is something really stupid but I've been bashing my head on this for over 2 hours without getting any result.

Anyone got any idea where the error could be located?

Thank you.

Upvotes: 1

Views: 211

Answers (2)

LSerni
LSerni

Reputation: 57398

Why not just do the other way round?

SELECT @TODAY := SELECT DATE_FORMAT(NOW(), '%Y%m%d');

SELECT ... FROM my_table WHERE @TODAY BETWEEN (mydate, mydate2);

This way you can also visually inspect @TODAY as well as mydate and mydate2 on the target row, and can use indexing on the date fields.

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Your custom aliases are not filtered by WHERE clause you need to use HAVING clause

SELECT
  UNIX_TIMESTAMP(STR_TO_DATE(mydate, '%Y%m%d')) as Date1,
  UNIX_TIMESTAMP(STR_TO_DATE(mydate2, '%Y%m%d')) as Date2
FROM
  my_table
HAVING
  (UNIX_TIMESTAMP() BETWEEN Date1 and Date2 )

or 

SELECT
  UNIX_TIMESTAMP(STR_TO_DATE(mydate, '%Y%m%d')) as Date1,
  UNIX_TIMESTAMP(STR_TO_DATE(mydate2, '%Y%m%d')) as Date2
FROM
  my_table
WHERE 
  (UNIX_TIMESTAMP() BETWEEN UNIX_TIMESTAMP(STR_TO_DATE(mydate, '%Y%m%d'))
  and 
  UNIX_TIMESTAMP(STR_TO_DATE(mydate2, '%Y%m%d')))

Upvotes: 2

Related Questions