Description:
Table Name : pageviews
Columns
* uri varchar(400)
* request_timestamp timestamp
Sample Data
/index.php 2012-07-31 18:18:19
/status.php 2012-08-02 06:05:20
/index.php 2012-08-03 22:43:22
I wanted to query all requests that were made 5 days back. I executed this query on August 4, 2012.
Query
SELECT * FROM pageviews WHERE DATE(request_timestamp) = CURRENT_DATE - 5
Expected Output:
All pageviews that were done five days back. But the query returned no rows. It should have returned the entry,
/index.php 2012-07-31 18:18:19
How to repeat:
Create the necessary table with the following query
CREATE TABLE pageviews(uri varchar(400),request_timestamp timestamp);
Create mock data for the table with the following queries
INSERT INTO temp VALUES ('/index.php','2012-07-31 18:18:19');
INSERT INTO temp VALUES ('/status.php','2012-08-02 06:05:20');
INSERT INTO temp VALUES ('/index.php','2012-08-03 22:43:22');
Query to Test
SELECT * FROM pageviews WHERE DATE(request_timestamp) = CURRENT_DATE - 5
The above query was executed when the current date was August 4, 2012.
Description: Table Name : pageviews Columns * uri varchar(400) * request_timestamp timestamp Sample Data /index.php 2012-07-31 18:18:19 /status.php 2012-08-02 06:05:20 /index.php 2012-08-03 22:43:22 I wanted to query all requests that were made 5 days back. I executed this query on August 4, 2012. Query SELECT * FROM pageviews WHERE DATE(request_timestamp) = CURRENT_DATE - 5 Expected Output: All pageviews that were done five days back. But the query returned no rows. It should have returned the entry, /index.php 2012-07-31 18:18:19 How to repeat: Create the necessary table with the following query CREATE TABLE pageviews(uri varchar(400),request_timestamp timestamp); Create mock data for the table with the following queries INSERT INTO temp VALUES ('/index.php','2012-07-31 18:18:19'); INSERT INTO temp VALUES ('/status.php','2012-08-02 06:05:20'); INSERT INTO temp VALUES ('/index.php','2012-08-03 22:43:22'); Query to Test SELECT * FROM pageviews WHERE DATE(request_timestamp) = CURRENT_DATE - 5 The above query was executed when the current date was August 4, 2012.