CODEWITHSUNDEEP
mysqlsqltimestamp
BonifatiusK
BonifatiusK

Reputation: 2331

MySQL select Current_timestamp between

I have a list of users in MySQL and on subscription the timestamp is set in the data base using the CURRENT_TIMESTAMP.

Now I want to do a select from this table where the subscribe date is between day X and day Y I tried several queries but somehow they all turn up empty.

Here is my last version

SELECT * 
FROM users 
WHERE subscribe_date BETWEEN '2013-10-07'AND '2013-13-10'

As I know for sure this date: 2013-10-08 14:38:49 is in the subscribe_data field It should turn up somehow

What is the best way to do this?

Maybe good to know my 'subscribe_date' column has type 'timestamp' and is auto filled with 'CURRENT_TIMESTAMP'

Here is the data in this table:

+----+-----------+---------------------+
| id | firstname | subscribe_date      |
+----+-----------+---------------------+
| 20 | Peter     | 2013-10-01 14:37:17 |
| 21 | Jack      | 2013-10-08 14:38:49 |
| 22 | Andrew    | 2013-10-10 14:41:03 |
| 23 | Margret   | 2013-10-14 14:42:46 |
+----+-----------+---------------------+

Upvotes: 1

Views: 2027

Answers (1)

Alma Do
Alma Do

Reputation: 37365

Since TIMESTAMP is up to seconds precision usually, you have to add time part:

SELECT * 
FROM users 
WHERE (subscribe_date BETWEEN '2013-10-07 00:00:00' AND '2013-12-10 23:59:59')

I've fixed your '2013-13-10' to '2013-12-10 23:59:59' since there's no 13-th month (and in DATETIME format it's YYYY-MM-DD, so month goes second)

Upvotes: 2

Related Questions