MySQL: Issue with JOIN ON timestamp and date

Question

I have two table and I need a (LEFT/RIGHT/INNER) JOIN on two date fields.

The transactions table has a timestamp data type. (with Adminer by the way I can read data in "human format", not in number, just as note for you).

Another one, the calendar table, has a date data type.

I'm going crazy trying to cast/convert the first timestamp to the date without success. The JOIN does not JOIN at all, like the two data are different type.

SELECT *
FROM transactions
RIGHT JOIN calendar ON calendar.day_date = DATE(transactions.dateTransaction)

I have 2 values in transactions and 100 in calendar, so I'm expecting 100 records -- 98 with NULL and 2 populated.

Of course this doesn't change also in:

SELECT *
FROM transactions
LEFT JOIN calendar ON calendar.day_date = DATE(transactions.dateTransaction)

or, also, with a double DATE:

SELECT *
FROM transactions
LEFT JOIN calendar ON DATE(calendar.day_date) = DATE(transactions.dateTransaction)

Or also:

SELECT *
    FROM calendar c
    RIGHT JOIN transactions t ON c.day_date = DATE(t.dateTransaction)

SELECT *
    FROM calendar c
    LEFT JOIN transactions t ON c.day_date = DATE(t.dateTransaction)

I'm getting only the 2 records on transactions, I'm expecting ~100 (100 on calendar).

Or also with a FROM_UNIXTIME.

transactions table schema:

CREATE TABLE `transactions` 
(   `idTransactions` int(11) NOT NULL AUTO_INCREMENT, 
    `dateTransaction` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`idTransactions`),
    KEY `index_dateTransaction` (`dateTransaction`) USING BTREE 
) ENGINE=MyISAM AUTO_INCREMENT=155731 DEFAULT CHARSET=utf8

calendar table schema

CREATE TABLE `calendar` 
(   `day_date` date DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

*I'm going crazy to cast/convert the first timestamp to the data* what are your table columns type? post your schema first — Alex, Oct 20 '15 at 17:27
did you try casting calendar.day_date to a date? aka `DATE(calendar.day_date)` also this is going to have problems. what happens when the timestamp field has more than one record on a particular date? it'll give you multiple records — John Ruddell, Oct 20 '15 at 17:31
@JohnRuddell Yes I did try, without success. In this specific case the 2 record on transaction are on different days. — sineverba, Oct 20 '15 at 17:33

David Soussan · Answer 1 · 2015-10-20T17:41:57.490

1

A MYSQL TIMESTAMP is not the same as a UNIX timestamp, it is a DATETIME column with specific additional behaviour. You can read the data because it is a datetime value, not because some magic conversion has taken place. Now if your other column is just a DATE datatype then they will not match: DATE <> DATETIME, To join on them means casting one to the same type as the other,as you have done in those queries.

So your real problem is the way you are building your query: if you want all calender with any transactions then:

SELECT *
    FROM calendar c
    LEFT JOIN transactions t ON c.day_date = DATE(t.dateTransaction)

TIMESTAMP definition: https://dev.mysql.com/doc/refman/5.1/en/datetime.html

DATE() definition: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date

edited Oct 20 '15 at 17:41

answered Oct 20 '15 at 17:36

David Soussan

2,698
1
16
19

thank you for your answer, but with your query I obtain only the 2 records from transactions. I'm missing the others 98/100 from calendar. – sineverba Oct 20 '15 at 18:08
I did try your QUERY. Both, with LEFT or RIGHT join, I'm getting only the 2 records on transactions. I think issue is on the convert... – sineverba Oct 20 '15 at 18:27
@sineverba I think issue is on DATA, post some proofs like data samples or screenshots. – Alex Oct 20 '15 at 18:55

score 0 · Answer 2 · edited May 23 '17 at 12:23

The only way I can guess what you issue is: you need FULL OUTER JOIN which is not provided by mysql.

But there is a trick: https://stackoverflow.com/a/4796911/4421474

So my guess is:

http://sqlfiddle.com/#!9/9aa42d/4

SELECT `transactions`.*,
   `calendar`.*
FROM `transactions`
LEFT JOIN `calendar` 
ON `calendar`.`day_date` = DATE(`transactions`.`dateTransaction`)
UNION
SELECT `transactions`.*,
   `calendar`.*
FROM `transactions`
RIGHT JOIN `calendar` 
ON `calendar`.`day_date` = DATE(`transactions`.`dateTransaction`);

MySQL: Issue with JOIN ON timestamp and date

2 Answers2