Regex in Mysql to fetch certain X tempo results

Asked

Viewed 159 times

0

I have a field in the table defined as DATE and the data is saved in this field as follows: year-month-day hour:minute:second 

Exemplo: 2014/04/22 18:32:00

I need to develop a query that returns me only given year/day/month.

A scheme:

IR PARA -> TABELA -> E só pegar o resultados que o ano de 2014 -> 2014-xx-xx     xx: xx:xx

Where X is a random value, that is, it returns any value to me, because it is the year 2014

In a way I can do this with the year/month/day ..

mysql regex query

  • I put an answer that does not use regex, in fact I imagine that regex would not bring any benefit in this case.

2 answers

3


For complete dates:

-- Se seu campo for DATE:
SELECT * FROM tabela WHERE DATE(minhadata) = '2014-05-21';
-- Se seu campo for VARCHAR:
SELECT * FROM tabela WHERE LEFT(minhadata,10) = '2014/05/21';

To year only:

-- Se o campo for DATE:
SELECT * FROM tabela WHERE YEAR(minhadata) = '2014';
-- Se o campo for VARCHAR:
SELECT * FROM tabela WHERE LEFT(minhadata,4) = '2014';

For month only:

-- Se o campo for DATE:
SELECT * FROM tabela WHERE MONTH(minhadata) = '05';
-- Se o campo for VARCHAR:
SELECT * FROM tabela WHERE SUBSTR(minhadata, 6, 2) = '05';

For day only::

-- Se o campo for DATE:
SELECT * FROM tabela WHERE DAY(minhadata) = '23';
-- Se o campo for VARCHAR:
SELECT * FROM tabela WHERE SUBSTR(minhadata, 9, 2) = '23';

Combining the above possibilities

Here’s an example of how to get the records for the 17th of every month of 2013:

-- Se o campo for DATE:
SELECT * FROM tabela WHERE
   YEAR(minhadata) = '2013' AND
   DAY(minhadata) = '17';
-- Se o campo for VARCHAR:
SELECT * FROM tabela WHERE
   LEFT(minhadata,4) = '2013' AND
   SUBSTR(minhadata, 9, 2) = '17';

Note that I am considering the date VARCHAR in format AAAA/MM/DD.
For other formats, adjust the SUBSTR(campo,posicaoinicial,quantidade) accordingly.

2

  • Yes, but my system keeps the date this way in a VARCHAR field and I need it that way, there are no other solutions?

  • Using different types is not recommended as it takes later work, being a CHAR field use the FUNCTION SUBSTR.

  • http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr

  • 2

    But its initial definition was that the field is of type DATE. What is the "type" of the field finally !?

  • I referred that the name of the field was DATE not its definition, pardon.

  • DATE AND DATE is reserved word, should not be used as field name. use something like CREATION_DATE or DATA_DE_CRIACAO

Show 1 more comment

Browser other questions tagged mysql regex query

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