Hello i trying to select result date from mysql  mytable:      id_room | check_in   | check_out     The problem is when i want to check reserved data betwwan date 1 to date 2. for example my room data:     id_room : 1 ; check_in : 2014/11/01 ; check_out : 2014/11/08   So i want to check my booking in, 2014/11/03 and 2014/11/04. In system its will be not resulted thats mean no booking in that date, but in actual life 2014/11/03 and 2014/11/04 must have been booked. Because im check_in in 2014/11/01 and check_out in 2014/11/08.  I'm using   SELECT * FROM tabel WHERE cek_in BETWEEN  '2014/11/03' AND '2014/11/04' OR  cek_out BETWEEN 2014/11/03' AND '2014/11/04'  So if there any clue or other logic, please  please tell me :D Thanks
6719
Given two date ranges, what is the simplest or most efficient way to determine whether the two date ranges overlap?  As an example, suppose we have ranges denoted by DateTime variables StartDate1 to EndDate1 and StartDate2 to EndDate2.
9891
I have a series of ranges with start dates and end dates. I want to check to see if a date is within that range.  Date.before() and Date.after() seem to be a little awkward to use. What I really need is something like this pseudocode:  boolean isWithinRange(Date testDate) {     return testDate &gt;= startDate &amp;&amp; testDate &lt;= endDate; }   Not sure if it's relevant, but the dates I'm pulling from the database have timestamps.
114524
How do I generate a range of consecutive numbers (one per line) from a MySQL query so that I can insert them into a table?  For example:  nr 1 2 3 4 5   I would like to use only MySQL for this (not PHP or other languages).
53567
I have a query which is meant to show me any rows in table A which have not been updated recently enough. (Each row should be updated within 2 months after "month_no".):  SELECT A.identifier      , A.name      , TO_NUMBER(DECODE( A.month_no              , 1, 200803               , 2, 200804               , 3, 200805               , 4, 200806               , 5, 200807               , 6, 200808               , 7, 200809               , 8, 200810               , 9, 200811               , 10, 200812               , 11, 200701               , 12, 200702              , NULL)) as MONTH_NO      , TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE   FROM table_a A      , table_b B  WHERE A.identifier = B.identifier    AND MONTH_NO &gt; UPD_DATE   The last line in the WHERE clause causes an "ORA-00904 Invalid Identifier" error. Needless to say, I don't want to repeat the entire DECODE function in my WHERE clause. Any thoughts? (Both fixes and workarounds accepted...)
74018
I am storing the response to various rpc calls in a mysql table with the following fields:  Table: rpc_responses  timestamp   (date) method      (varchar) id          (varchar) response    (mediumtext)  PRIMARY KEY(timestamp,method,id)   What is the best method of selecting the most recent responses for all existing combinations of method and id?   For each date there can only be one response for a given method/id. Not all call combinations are necessarily present for a given date. There are dozens of methods, thousands of ids and at least 365 different dates    Sample data:  timestamp  method  id response 2009-01-10 getThud 16 "....." 2009-01-10 getFoo  12 "....." 2009-01-10 getBar  12 "....." 2009-01-11 getFoo  12 "....." 2009-01-11 getBar  16 "....."   Desired result:  2009-01-10 getThud 16 "....." 2009-01-10 getBar 12 "....." 2009-01-11 getFoo 12 "....." 2009-01-11 getBar 16 "....."   (I don't think  is the same question - it won't give me the most recent response)
73453
I've already submitted my UK visa application, but have yet to receive the visa.  I need to book a flight a day or two before the 'intended date of arrival' that I've entered in my application.  My question is, if and when I get the visa, will the validity be from the date it was issued, or from the date of intended date of arrival I entered on my application.  In case it's from the intended date, then is it possible for me to arrive a day or two before that day, or will it create problems with the airport security?
9505
Let me explain you the problem   $ date +%c -d "$d" Tue 31 Dec 2013 01:13:06 PM CET $ date +'Today is %F' -d "$d" Today is 2013-12-31   This solution corresponds to current date.  But I have one variable which stores date other than current date  $Prev_date="Wed Dec 25 06:35:02 EST 2013"    I am looking for solution to read this date as 2013-12-25 and store it in a variable.  I have tried this:  a=`date --date=$Prev_date '+%y/%m/d'` echo $a   It's giving this error:  date: illegal option -- date=Wed usage: date [-u] mmddHHMM[[cc]yy][.SS] date [-u] [+format] date -a [-]sss[.fff]
57472
I would like to construct a query that displays all the results in a table, but is offset by 5 from the start of the table. As far as I can tell, MySQL's LIMIT requires a limit as well as an offset. Is there any way to do this?
60004
Is it possible to convert a timestamp to a date format in a query?  This is the code I am using.  $query = db_select('my_submissions', 's'); $query-&gt;fields('s', array('sid','name','company','email','phone','is_customer','submitted','count')); $entries = $query-&gt;execute();  $rows = array(); foreach ($entries as $entry) {   $row = array_map('check_plain', (array) $entry);   $row['submitted'] = $row['submitted'] ? date('d.m.Y H:i', $row['submitted']) : "";   $rows[] = $row; }
103971
CREATE TABLE plan    (       id                VARCHAR(30) NOT NULL,       name              VARCHAR(20),       amount            INT,       interval          INT,       currency          CHAR(3),       object            VARCHAR(20),       livemode          BOOLEAN,       interval_count    INT,       trial_period_days INT,       created           TIMESTAMP,       updated           TIMESTAMP,       deleted           TIMESTAMP,       PRIMARY KEY (id)    );    Produces   'ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'interval          INT,       currency          CHAR(3),       object            ' at line 6'   Doesn't work. However if the 'l' at the end of 'interval' is removed. It works.  CREATE TABLE plan    (       id                VARCHAR(30) NOT NULL,       name              VARCHAR(20),       amount            INT,       interva           INT,       currency          CHAR(3),       object            VARCHAR(20),       livemode          BOOLEAN,       interval_count    INT,       trial_period_days INT,       created           TIMESTAMP,       updated           TIMESTAMP,       deleted           TIMESTAMP,       PRIMARY KEY (id)    );    I don't understand if this is a problem with attempting to use 'interval' and 'interval_count'. What's going on here.
51700
MySQL column > sdate, edate ( its 2 column).  sdate is start date for project starting and edate is end date for project ending.  so i need to make search between them..  &lt;strong&gt;Search&lt;/strong&gt;&lt;br /&gt; &lt;form method="post" action="search.php"&gt;   Start Report Date : &lt;input type="text" name="sdate" /&gt;   End Report Date : &lt;input type="text" name="edate" /&gt;   &lt;input type="submit" name="Submit" value="Search" /&gt; &lt;/form&gt;  This is example data in mysql sdate             Project Name      edate 22 December 2008  project 1         23 December 2008 25 December 2008  project 2         26 December 2008 24 December 2008  project 3         27 December 2008 1  January 2008   project 4         20 January 2008 10 December 2008  project 5         12 December 2008   so let say a user entered sdate ( eg, 22 December 2008 ) and edate ( eg, 30 December 2008 ).  It should display  22 December 2008  project 1         23 December 2008 25 December 2008  project 2         26 December 2008 24 December 2008  project 3         27 December 2008   So i need a php code sql query which should display entries lies between those 2 dates..  Please help me..   Thanks very much..
115910
When I view the result of a SELECT with MySQL Workbench, it's correct with one single \  max@host 10:13:58: ~$ mysql -h db-master.domain.local -uuser -ppw db -e ' &gt;                 SELECT &gt;                 DISTINCT i.filesourceregexp &gt;                 FROM db.ImportLogFiles i'  +------------------------------------------------+ | filesourceregexp                               | +------------------------------------------------+ | ^[0-9]{8}_1062355673_merge_google_pbn\.csv$    | | ^[0-9]{8}_8026062435_merge_google_pbn\.csv$    | | ^[0-9]{8}_1062355673_store_visits_report\.csv$ | +------------------------------------------------+  max@host 10:14:10: ~$ mysql -h db-master.domain.local -uuser -ppw db -e '                 SELECT                 DISTINCT i.filesourceregexp                 FROM db.ImportLogFiles i' | tail -n +2 ^[0-9]{8}_1062355673_merge_google_pbn\\.csv$ ^[0-9]{8}_8026062435_merge_google_pbn\\.csv$ ^[0-9]{8}_1062355673_store_visits_report\\.csv$ max@host 10:14:19: ~$    I have these options in my.cnf:  [client]  host = db-master  user = user  password = pass  default-character-set=utf8   Why does piping the result through tail change the output/string? (note the double \).
115374
I'm trying to write a stored procedure to select employees who have birthdays that are upcoming.    SELECT * FROM Employees WHERE Birthday &gt; @Today AND Birthday &lt; @Today + @NumDays  This will not work because the birth year is part of Birthday, so if my birthday was '09-18-1983' that will not fall between '09-18-2008' and '09-25-2008'.    Is there a way to ignore the year portion of date fields and just compare month/days?   This will be run every monday morning to alert managers of birthdays upcoming, so it possibly will span new years.    Here is the working solution that I ended up creating, thanks Kogus.    SELECT * FROM Employees  WHERE Cast(DATEDIFF(dd, birthdt, getDate()) / 365.25 as int)     - Cast(DATEDIFF(dd, birthdt, futureDate) / 365.25 as int)  &lt;&gt; 0
80455
Let's say  this is the sample date coming from a join of 2 tables. Database is Postgres 9.6  id  product_id  invoice_id  amount       date 1    PROD1       INV01       2          01-01-2018 2    PROD2       INV02       3          01-01-2018 3    PROD1       INV01       2          05-01-2018 4    PROD1       INV03       1          05-01-2018 5    PROD2       INV02       3          08-01-2018 6    PROD2       INV04       4          08-01-2018   I want to know if it's possible in a optimized way to:   Get all the PRODx with their respective INVx which have the latest date, but per product_id. Please note that records unused from a day may be reported to a new one. This means:    id  product_id  invoice_id  amount       date 3    PROD1       INV01       2          05-01-2018 4    PROD1       INV03       1          05-01-2018 5    PROD2       INV02       3          08-01-2018 6    PROD2       INV04       4          08-01-2018     Get daily summed amounts for each PRODx but fill the gaps with the previous ones if day does not exist.   This means:   product_id    amount       date    PROD1         2          01-01-2018    PROD2         3          01-01-2018    PROD1         2          02-01-2018    PROD2         3          02-01-2018    PROD1         2          03-01-2018    PROD2         3          03-01-2018    PROD1         2          04-01-2018    PROD2         3          04-01-2018    PROD1         3          05-01-2018    PROD2         3          05-01-2018    PROD1         3          06-01-2018    PROD2         3          06-01-2018    PROD1         3          07-01-2018    PROD2         3          07-01-2018    PROD1         3          08-01-2018    PROD2         7          08-01-2018   A few thoughts:   For first question I could obtain the max(date) for each PRODx and the pick for each PRODx the rows that have the date=with max(date) but I was wondering if there's faster way to obtain this given a large number of recors in the database For the second question, I could generate a series of dates for the interval needed and then use WITH rows As and do the query grouping by product_id and sum by amount and then select for each date the previous values from rows with a limit 1 but that does not sound that optimized either.   Looking forward for any input. Thank you.  Later edit: Trying to give DISTINCT ON () a try.   If I have distinct on(product_id, invoice_id) then I don't get only the most recent ones for the most recent date. If there were invoice_ids in the the past, beside the latest date, then they will be returned If I have distinct on (product_id) then it returns from the most recent date, but as normal, only the last rows even if in the last day I have two positions for PROD1.    Basically I need something like 'I need for the most recent date, all the product_ids and their invoice_ids while keeping in mind that a product_id can have multiple invoice_ids'  Later edit 2:  Running a query like for first question seems to be reasonably fast:  select product_id, invoice_id, amount from mytable inner join myOtherTable on...              inner join (select max(date) as last_date, product_id                           from mytable                           group by product_id) sub on mytable.date =                           sub.last_date
30879
I'm trying to use a select statement to get all of the columns from a certain MySQL table except one. Is there a simple way to do this?  EDIT: There are 53 columns in this table (NOT MY DESIGN)
23049
Using standard mysql functions is there a way to write a query that will return a list of days between two dates.  eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values:   2009-01-01   2009-01-02   2009-01-03  2009-01-04   2009-01-05  2009-01-06  2009-01-07  2009-01-08   2009-01-09  2009-01-10  2009-01-11  2009-01-12  2009-01-13   Edit: It appears I have not been clear. I want to GENERATE this list. I have values stored in the database (by datetime) but want them to be aggregated on a left outer join to a list of dates as above (I am expecting null from the right side of some of this join for some days and will handle this).
116805
Schema:          Column        |           Type            ----------------------+--------------------------  id                   | integer                    event_id             | integer                    started_at           | timestamp with time zone   ended_at             | timestamp with time zone   created_at           | timestamp with time zone      "event_seat_state_lookup_pkey" PRIMARY KEY, btree (id)     "event_seating_lookup_created_at_idx" btree (created_at)     "event_seating_lookup_created_at_idx2" gist (created_at)   Query:  SELECT id FROM event_seating_lookup esl1 WHERE   tstzrange(now() - interval '1 hour', now() + interval '1 hour', '[)') @&gt; esl1.created_at;   Explain analyze:  Table with &lt;100k rows.   Seq Scan on event_seating_lookup esl1  (cost=0.00..1550.30 rows=148 width=4) (actual time=0.013..19.956 rows=29103 loops=1)    Filter: (tstzrange((now() - '01:00:00'::interval), (now() + '01:00:00'::interval), '[)'::text) @&gt; created_at)    Buffers: shared hit=809  Planning Time: 0.110 ms  Execution Time: 21.942 ms   Table with 1M+ rows:  Seq Scan on event_seating_lookup esl1  (cost=10000000000.00..10000042152.75 rows=5832 width=4) (actual time=0.009..621.895 rows=1166413 loops=1)   Filter: (tstzrange((now() - '01:00:00'::interval), (now() + '01:00:00'::interval), '[)'::text) @&gt; created_at)   Buffers: shared hit=12995 Planning Time: 0.092 ms Execution Time: 697.927 ms   I have tried:  VACUUM FULL event_seating_lookup; VACUUM event_seating_lookup; VACUUM ANALYZE event_seating_lookup; SET enable_seqscan = OFF;   Problem:  event_seating_lookup_created_at_idx or event_seating_lookup_created_at_idx2 indexes are not being used.  Notes:   PostgreSQL 11.1. btree_gist extension is installed. I have tried equivalent setup with created_at timestamp without time zone and using tsrange; same result. I understand that rewriting the query with &gt;=, &lt; checks would make it use the btree index. The question is what is the reason the index is not used with the tstzrange containment operator and if there is a way to make it work.