-2

mysql workbenchRetrieving data from database by using this condition from Tuesday evening 5 pm to Friday 5 pm, and Friday 5 pm to Tuesday 5 pm how to write this query in the database?

I should not use specific time and date to get data it should be an automatic process.

without specifying the date and time I should get constantly

it may be asked but I didn't get what I need.

Vishnu T
  • 1
  • 2
  • 2
  • 4
    Possible duplicate of [mysql: get record count between two date-time](https://stackoverflow.com/questions/5786649/mysql-get-record-count-between-two-date-time) – vahdet Mar 26 '18 at 14:06
  • https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html – Shawn Mar 26 '18 at 14:07
  • 1
    Please share some sample data and your desired results. I'm pretty certain if I were to filter for just records between "Tuesday 5pm and Friday 5pm" AND "Friday 5pm and Tuesday 5pm" that would include ALL records. Furthermore, you say "Didn't get what I needed" from other similar questions, but you don't share why those didn't fulfill your need. Without further clarification I can't imagine you will get some new amazing answer here that isn't he same as using a BETWEEN condition on your date (possibly in a CASE statement) just like all the other similar questions – JNevill Mar 26 '18 at 14:09
  • JNevill I added the attachment see it may help – Vishnu T Mar 26 '18 at 14:21
  • @vahdet despite it's really unclear, I don't think that question answers what he/she tried to ask here. If I understood it well, it'll be something like what [I've posted as an answer](https://stackoverflow.com/a/49493989/8093394). – Diego Rafael Souza Mar 26 '18 at 14:37

2 Answers2

0

It's simply impossible to help you with a useful answer if you don't ask precisely what you've attempt or a Minimal, complete and verifiable example at least.

But I'll try:

select Dayofweek(date), time(date) , date
from `table` t
where Dayofweek(date) >= 3 
   and Dayofweek(date) <= 6
   and ((Dayofweek(date) = 3 and time(date) > '17:00:00') or
        (Dayofweek(date) = 6 and time(date) < '05:00:00') or
        (Dayofweek(date) > 3 and Dayofweek(date) < 6  ))

Check it on sql fiddle.

Diego Rafael Souza
  • 5,241
  • 3
  • 23
  • 62
  • I'm developing an e-commerce website on that website I have a condition. The orders table data should be divided into two segments one is Tuesday to friday one batch and friday to tuesday one batch, the orders list should be taken from that condtion. The customer who all ordered after tuesday 5 pm to friday 5pm i need to get one list and same friday to tuesday another list. This should be done every week how to write a query for that – Vishnu T Mar 26 '18 at 14:58
0

I think the biggest problem here is the representation of created_time.

Looks like that is not a MySQL DATETIME or TIMESTAMP column, but is a character datatype.

And the date portion is not represented in a canonical year-month-day format, such that a string comparison is equivalent to a date range comparison.

Doing a comparison of 'dd-mm-yyyy' >= '20-03-2018', we're going to get the 21st, 22nd, ... 28th, 29th, 30th of every month, of every year.

e.g. for a string comparison,

'22-01-2018' > '20-03-2018'

even though January 1 is not after March 20. In comparing the strings, we're comparing character by character, not looking at a "date" comparison.

Without changing the data structure... if the created_time column is stored as a character string in 'dd-mm-yyyy hh:mi' format, then in the query, we can convert the strings to DATETIME datatypes using the STR_TO_DATE function...

Or, maybe I'm barking up the wrong tree.

References:

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

As a demonstration:

SELECT STR_TO_DATE('20-03-2018 20:13','%d-%m-%Y %H:%i')

As an example of how to retrieve 5pm Tuesday up to 5pm Friday:

SELECT ... 
  FROM fb
 WHERE STR_TO_DATE( fb.created_time ,'%d-%m-%Y %H:%i') >= '2018-03-20 17:00'
   AND STR_TO_DATE( fb.created_time ,'%d-%m-%Y %H:%i') <  '2018-03-23 17:00'

performance note: this query pattern is forcing MySQL to evaluate the STR_TO_DATE function on every row in fb (a full scan). If created_time were a DATETIME datatype, we could avoid the wrapper function, and we could have a query that allows MySQL to use a range scan operation on an index with created_time as a leading column.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • can you tell me a query for MySQL(format) how to get that data I can try with different schema spencer – Vishnu T Mar 26 '18 at 16:31