CODEWITHSUNDEEP
mysql
Paul
Paul

Reputation: 654

MySQL compare timestamp gives error

I'm trying to write a SQL query but I keep getting an error and I have no clue on what could be wrong. I read the manual and searched a lot for it, but I can't get it working.

Query:

SELECT appid
FROM steam_app
ORDER BY last_update, appid
WHERE last_update < '2014-10-16 01:01:01'

This keeps giving an error SQL Error 1064, SQL syntax. Now I know the error is on the WHERE line. Because if I remove it, all is well.

Solution: the problem is the order of WHERE and ORDER BY. WHERE should be above ORDER BY.

What I want in the end is a query like:

SELECT appid
FROM steam_app
ORDER BY last_update, appid
WHERE last_update < NOW() + INTERVAL 7 DAY

My database:

CREATE TABLE `steam_app` (
    `appid` INT(10) UNSIGNED NOT NULL,
    `name` VARCHAR(128) NULL DEFAULT NULL,
    `type` VARCHAR(64) NULL DEFAULT NULL,
    `header_image` VARCHAR(256) NULL DEFAULT NULL,
    `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `last_change` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`appid`),
    INDEX `type` (`type`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Sample data

INSERT INTO `steam_app` (`appid`, `name`, `type`, `header_image`, `last_update`, `last_change`) VALUES (327400, 'Sid Meier\'s Colonization (Classic)', 'game', 'http://cdn.akamai.steamstatic.com/steam/apps/327400/header.jpg?t=1412883714', '2014-10-16 13:36:37', '2014-10-16 13:36:37');
INSERT INTO `steam_app` (`appid`, `name`, `type`, `header_image`, `last_update`, `last_change`) VALUES (327650, 'May’s Mysteries: The Secret of Dragonville', 'game', 'http://cdn.akamai.steamstatic.com/steam/apps/327650/header.jpg?t=1413366276', '2014-10-16 13:36:37', '2014-10-16 13:36:37');
INSERT INTO `steam_app` (`appid`, `name`, `type`, `header_image`, `last_update`, `last_change`) VALUES (327860, 'Salt', 'game', 'http://cdn.akamai.steamstatic.com/steam/apps/327860/header.jpg?t=1413399075', '2014-10-16 13:31:53', '2014-10-16 13:31:53');

Upvotes: 1

Views: 239

Answers (1)

Mureinik
Mureinik

Reputation: 311163

You need to use the date_add function. Additionally, the order by clause comes after the where clause:

SELECT   last_update, appid
FROM     steam_app
WHERE    last_update < DATE_ADD(NOW(), INTERVAL 7 DAY)
ORDER BY last_update, appid

Upvotes: 1

Related Questions