Show only larger records two days before today

Asked

Viewed 66 times

1

I have the following Query in MYSQL:

SELECT id_tabela,titulo,data_gabarito,left(data_gabarito,10) AS d_gabarito FROM tabela 
WHERE data_gabarito RLIKE('^[0-9]{2}/[0-9]{2}/[0-9]{4}') 
ORDER BY str_to_date(d_gabarito,'%d/%m/%Y') DESC
  • It takes a data_feedback field that has strings with various dates and formats and displays only the first ones through the regex. (12/02/2019, 11/02/2019, 15/02/2019) takes only the first date.
  • It converts the alias (d_jig) to date and sorts by higher date.

What I want:

Display only larger records dating two days before today. Example: Today is 07/02/2019, I would like to display only the records with date from two days prior to today’s date (now), ie larger than 05/02/2017.

Explaining more...today is Thursday (07/02/2019) would like to display only records that have larger dates 05/02/2019.

The problem is that you cannot use the ALIAS "AS" in where to do. d_jig>now-2 for example.

I thought I’d create a view for this...but perhaps someone sees a simpler solution.

mysql regex

2 answers

1


Put your WHERE this way:

WHERE DATEDIFF(STR_TO_DATE(LEFT(data_gabarito,10),'%d/%m/%Y'), NOW()) >= -2

  • the problem that this data_template is a string that has several formats. There’s a date, there’s text, etc... so I pass it on a regex in the Where to get only the date.

  • worked well put your Where with an AND and kept my regex. Thanks.

1

Example: I want to get the id and date of the tickets that are dated today and two days ago, so:

select id, data from tb_boleto where data BETWEEN curdate() AND DATE_SUB(curdate(), INTERVAL 2 DAY);

In select above I use the command kurdish() that takes the current day, the command DATE_SUB decreases the date range, I pass the current date and the date range I want to decrease which is 2. See other Mysql functions for date in this link or check the Mysql DO documentation here.

Now just apply in your query.

  • happens that my date does not come from a field, comes treated by a regex and transformed into an alias.

  • Your d_feedback is what I would call date in the example I gave, only apply the function shown with it.

  • turns out not, as said this d_jig is a field treated by a regex and that turns an alias. Can not use alias in WHERE. mysql does not allow.

  • Your d_feedback is what I would call date in the example I gave, only apply the function shown with it. WHERE data_gabarito RLIKE('^[0-9]{2}/[0-9]{2}/[0-9]{4}') AND str_to_date(d_gabarito,'%d/%m/%Y') BETWEEN curdate() AND DATE_SUB(curdate(), INTERVAL 2 DAY). Same way you turn it into date to order, turn it into date and make the between.

  • 1

    perfect. thanks dude.

Browser other questions tagged mysql regex

You are not signed in. Login or sign up in order to post.