Clever Geek Handbook

Request for LocalDateTime

I have a table containing records with DateTime datatype in MySQL (for example 2013-01-17 21:16:06), while on my entity I am selecting the LocalDateTime datatype for the date field. At the time of my queries, I would like to get all records based on 2 dates only (fromDate and toDate using datepicker). Lets say I selected the same date as fromDate and toDate, the problem would be the same as the time is 00:00:00 hence no result from the database.

I'm curious if I was using the correct datatype from Joda. Should I adjust the time for both values, say 00:00:00 for fromDate and 23:59:59 for toDate? Or what's the best approach?

+3


source to share


1 answer


For a datetime column and two arbitrary dates as input, you can write:

SELECT * FROM `table`
WHERE `datetime` >= @startDate
AND   `dateTime` <  @endDate + INTERVAL 1 DAY

Where:

  • startDate is the start date, inclusive
  • endDate - end date, exclusive


What is listed above is best explained in the following cases:

  • To view all records for 2013-01-17

    (one day) startDate must be 2013-01-17

    and endDate must be2013-01-17

  • To see all records for 2013-01-17

    - 2013-01-18

    (two days), startDate must be 2013-01-17

    and endDate must be 2013-01-18

    .

This makes date selection intuitive. The sign <

ensures that the calculated end date is excluded from the results (for example, in the first case, the query omits "2013-01-18 00:00:00 records")

+2


source






More articles:


All Articles