CODEWITHSUNDEEP
mysql
sineverba
sineverba

Reputation: 5172

MySQL: Issue with JOIN ON timestamp and date

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

Upvotes: 1

Views: 4939

Answers (2)

Alex
Alex

Reputation: 17289

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`);

Upvotes: 0

David Soussan
David Soussan

Reputation: 2736

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

Upvotes: 1

Related Questions