MYSQL - SQL date intervals query

I am managing an event database. Every event has a start and end timestamp (INT, unix timestamp).

Currently i'm able to do the following things with a single SQL query:

  • build up a calendar, and mark days when an event occurring
  • list occurring events on a given date (YYYY/MM/DD, YYYY/MM)

The problem is when an event spans several days i can't list it on a date between it's start and end timestamps.

For example:

Event starts on 2011/05/25 and ends on 2011/05/27 i can't list it on the page 2011/05/26.

My actual SQL query is

SELECT * FROM `event` 
WHERE (`start` BETWEEN ? AND ?) OR (`end` BETWEEN ? AND ?) 
ORDER BY start ASC

The two bound parameters (unix timestamps) are automatically calculated depending on what kind of parameter given (a whole month, or a specific day)

Is it possible to get these events (that spans several days) on a day between it's two endpoints extending my query above?

Please let me know if i can clarify my question.

Update

Example:

event start: 1309125660 (2011-06-27 00:01:00)
end end: 1314050340 (2011-08-22 23:59:59)

select start: 1312408860 (2011-08-04 00:01:00)
select end: 1312495199 (2011-08-04 23:59:59)

This event won't appear when i trying to list events occurring 2011/08/4

This question and answers originated from www.stackoverflow.com
Question by (5/26/2011 11:48:41 AM)

Answer

If I get the question right, with [:start, :end] being your date range of interest, you're looking for:

select *
 from event
where -- event started earlier, ends later
      start <= :start and :start <= end
   or -- event starts during [:start, :end]
      :start <= start and start <= :end
   or -- event ends during [:start, :end]  
      :start <= end and end <= :end;

If you're looking for a particular :day, use :day as :start and :day + 1 day as :end.

Answer by
Find More Answers
Related Topics  mysql  sql  date  select
Related Questions

  • sql query date time

    i have a datestamp field in my table structure. using sql, i want to find any user who registered in yesterdays date using these time range. eg: 2010-02-06 14:00:00 2010-02-07 10:00:00 i will…

  • Find conflicted date intervals using SQL

    Suppose I have following table in Sql Server 2008: ItemId StartDate EndDate 1 NULL 2011-01-15 2 2011-01-16 2011-01-25 3 2011-01-26 NULL As you can see, this table has StartDate and …

  • Merging date intervals in SQL Server

    I have the following data: StartDate | EndDate ------------------------- 1982.03.02 | 1982.09.30 1982.10.01 | 1985.01.17 1985.06.26 | 1985.07.26 1985.07.30 | 1991.12.31 1992.01.01 | 1995.12.…

  • Mysql Date to timestamp SQL Query

    I have release date in database as: release_date 2010-12-02 00:00:00 and i have to get 50 rows from the data order by release date desc. Can anyone tell me how can i implement this in mysql s…

  • I have a table which contains data with a date column. Mysql Query help needed!

    Now, I am using php timestamp (time()) in my columns as a timestamp. I use it because I thought that storing it as integer would be efficient than using datetime. Now I am having serious problems…

  • Date search Sql Query

    im storing the timestamp in mysql database in (INT) column, And i want to search the rows with between the dates. Anyone would please help what should be the Sql query to find the rows between two d…

  • Select mysql query between date?

    How to select data from mysql table past date to current date? For example, Select data from 1 january 2009 until current date ?? My column "datetime" is in datetime date type. Please help, thank…

  • Sql query join nearest date

    I have a sqlite query that I'm trying to write. I have two tables: TableA (sales): id sales date TableB (goals): id goal date I'm selecting from TableA like this: SELECT id,sales,date FROM …

  • SQL Select Query with single field as formatted and remaining fields as wildcard

    I'm trying to fetch records from a table in MySQL 5.5 (Community Server) which has 22 fields in total, the first field is of type DATETIME , and I want that field formatted with DATE_FORMAT() method…

  • Date Range SQL Query

    Schedule_ID---Card No----FromDate----ToDate 4-------------1000058----01-Aug-10---31-Aug-10 5-------------1000058----01-Sep-10---30-Sep-10 6-------------1000058----06-Oct-10---26-Oct-10 7-----------…