CODEWITHSUNDEEP
mysql
Jorg Ancrath
Jorg Ancrath

Reputation: 1447

Adding a date format to FROM_UNIXTIME breaks query

Trying to run this query:

SELECT FROM_UNIXTIME(offers_consumers_history.date,
                     `'%d-%m-%Y')` AS date,
                     COUNT(*) AS COUNT
FROM (`offers_history`)
JOIN `offers_consumers_history` ON `offers_consumers_history`.`offer_history_id`=`offers_history`.`id`
WHERE `offers_history`.`merchant_id` = 1
  AND `offers_history`.`offer_type_id` = 1
GROUP BY DATE(FROM_UNIXTIME(offers_consumers_history.date))
ORDER BY `offers_consumers_history`.`date`

If I run it without a date format on my first FROM_UNIXTIME (the %d-%m-%Y part), everything runs fine, but I'm obviously not getting the right date format displayed, the returned error is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (`offers_history`) JOIN `offers_consumers_history` ON `offers_consumers_his' at line 2

Without a date format I'm getting results such as:

{"date":"2010-08-18 18:40:00","count":"2"}

What I want is:

{"date":"18-08-2010","count":"2"}

Upvotes: 1

Views: 4134

Answers (2)

Code Lღver
Code Lღver

Reputation: 15603

Remove the () from the table name and change the alias name of count:

SELECT FROM_UNIXTIME(offers_consumers_history.date,
                     '%d-%m-%Y') AS `date`,
                     COUNT(*) AS cnt
FROM `offers_history`
JOIN `offers_consumers_history` ON `offers_consumers_history`.`offer_history_id`=`offers_history`.`id`
WHERE `offers_history`.`merchant_id` = 1
  AND `offers_history`.`offer_type_id` = 1
GROUP BY DATE(FROM_UNIXTIME(offers_consumers_history.date))
ORDER BY `offers_consumers_history`.`date`

Upvotes: 0

Yogesh Suthar
Yogesh Suthar

Reputation: 30488

count is a reserved word, you can't use it as alias, use different name

'%d-%m-%Y') AS dt,
COUNT(*) AS cnt

OR with backticks

(offers_consumers_history.date,'%d-%m-%Y') AS `date`,
COUNT(*) AS `COUNT`

Upvotes: 1

Related Questions