MySQL TimeStamp query

Question

I am trying to search for studentID within a date range. I only have one date in my database, therfore i only want the users to input one date, rather than having them input a start date and an end date for: WHERE timeStamp BETWEEN startDate AND endDate

So i am trying this...

SELECT * FROM scansTable
INNER JOIN registeredUsers ON scansTable.studentID = registeredUsers.id
INNER JOIN labSession ON scansTable.labSessionID = labSession.id
INNER JOIN staffTable ON labSession.lecturer = staffTable.id
INNER JOIN unitTable ON labSession.unit = unitTable.id
WHERE studentID = '10'
AND labSession.StartTimeStamp BETWEEN '2011 -05 -30'+00:00:00
AND '2011 -05 -30'+23:59:59;

But it is not returning anything when i know for sure there is a student of id 10 and that date range in the database

Am i doing the +00:00:00 wrong??

thanks

AJ. · Accepted Answer · 2011-05-31 19:44:38Z

4

Remove the spaces and plus symbols:

BETWEEN '2011-05-30 00:00:00' AND '2011-05-30 23:59:59'

answered May 31, 2011 at 19:44

AJ.

28.3k19 gold badges89 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tadeck · Accepted Answer · 2011-05-31 20:07:07Z

2

It seems you are using something like '2011 -05 -30'+00:00:00, where you should use '2011-05-30 00:00:00' (and make corresponsing changes to the second condition), because TIMESTAMP format (I assume this field is in this format) is YYYY-MM-DD HH:MM:SS.

Did it help? If not, give use the definition of the table plus the example row (at least both timestamp columns).

EDIT:

If you wanted to concatenate, you should have used CONCAT() function (see MySQL's documentation). It would look like this:

CONCAT('2011-05-30',' 00:00:00')

or, more meaningfully:

CONCAT_WS(' ','2011-05-30','00:00:00')

edited May 31, 2011 at 20:07

answered May 31, 2011 at 19:44

Tadeck

138k28 gold badges155 silver badges201 bronze badges

Comments

stevevls · Accepted Answer · 2011-05-31 19:43:58Z

2

If you haven't changed the default date format, remove the + signs and the extra spaces.

labSession.StartTimeStamp BETWEEN '2011-05-30 00:00:00' AND '2011-05-30 23:59:59';

Also, I don't know if it's a byproduct of the copy/paste, but MySQL won't even run the query as-is. The time portion of your timestamp needs to be within the quotes.

answered May 31, 2011 at 19:43

stevevls

10.9k1 gold badge50 silver badges51 bronze badges

Comments

Randy · Accepted Answer · 2011-05-31 19:45:21Z

0

between might not include that lower bound as an =

i believe it does include the upper bound as =, this might differ depending on the database.

answered May 31, 2011 at 19:45

Randy

16.7k2 gold badges40 silver badges56 bronze badges

Collectives™ on Stack Overflow

MySQL TimeStamp query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related