I am writing a sql query to retrieve the records from date range of a month but I did not get the result. Below is my query that I tried to run.

SELECT uid FROM sessions WHERE TIMESTAMP >= '2021-10-01 00:00:00' AND TIMESTAMP < '2021-10-31 00:00:00'

timestamp column contain value in numeric see link:

CREATE TABLE sessions ( uid int(10) unsigned NOT NULL COMMENT 'The users.uid corresponding to a session, or 0 for anonymous user.', sid varchar(128) CHARACTER SET ascii NOT NULL COMMENT 'A session ID (hashed). The value is generated by Drupal''s session handlers.', hostname varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The IP address that last used this session ID (sid).', timestamp int(11) NOT NULL DEFAULT '0' COMMENT 'The Unix timestamp when this session last requested a page. Old records are purged by PHP automatically.', session longblob COMMENT 'The serialized contents of $_SESSION, an array of name/value pairs that persists across page requests by this session ID. Drupal loads $_SESSION from here at the start of each request and saves it at the end.', PRIMARY KEY (sid), KEY timestamp (timestamp), KEY uid (uid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Drupal''s session handlers read and write into the sessions…'

CodePudding user response：

Use UNIX_TIMESTAMP to convert the datetime strings to unix timestamp numbers

SELECT uid 
FROM sessions 
WHERE `TIMESTAMP` >= UNIX_TIMESTAMP('2021-10-01 00:00:00')
  AND `TIMESTAMP` <= UNIX_TIMESTAMP('2021-10-31 23:59:59')

CodePudding user response：

First of all, you should'nt use 'TIMESTAMP' as a column name. But I think if only one example. If not, change it.

You have some options.

Using BETWEEN command

SELECT uid FROM sessions WHERE TIMESTAMP BETWEEN '2021-10-01 00:00:00' AND '2021-10-31 00:00:00'

Using MONTH and YEAR

SELECT uid FROM sessions WHERE MONTH(TIMESTAMP) = MONTH('2021-10-01 00:00:00') AND YEAR(TIMESTAMP) = YEAR('2021-10-01 00:00:00')