CODEWITHSUNDEEP
mysql
Adrien Hingert
Adrien Hingert

Reputation: 1516

MySQL date columns in day, month year

I have a MySQL table (A) which has, amongst others, three columns named "day", "month" and "year" in each of which is stored a number which, when read together, will represent a date. I have to join this table with another table (B) in which the date column is in UNIX format. Is there an easy way which would allow me to compare the two dates from the two tables in an easy way?

Upvotes: 0

Views: 4050

Answers (2)

Zane Bien
Zane Bien

Reputation: 23125

You can do:

SELECT *
FROM tbl a
INNER JOIN tbl b 
    ON UNIX_TIMESTAMP(CAST(CONCAT(a.year, '-', a.month, '-', a.day) AS DATETIME)) = b.unixtimecol

But the unix timestamp column in tableB is more precise to the second, whereas the columns in your tableA can only be precise to the day. As a result, it may not always join when you want it to.

If you want it to join where the two times are within the same day, you can also make the unix time column a DATE type so that both are only precise to the day:

SELECT *
FROM tbl a
INNER JOIN tbl b 
    ON CAST(CONCAT(a.year, '-', a.month, '-', a.day) AS DATE) = DATE(FROM_UNIXTIME(b.unixtimecol))

Upvotes: 2

Jirka Kopřiva
Jirka Kopřiva

Reputation: 3099

You can manage values with connection of fields

SELECT CONCAT(year, '-', month, '-', day) as u_date ...

or

SELECT ... WHERE CONCAT(A.year, '-', A.month, '-', A.day) > B.another_field

etc ...

Upvotes: 2

Related Questions