Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Filter by datetime MYSQL formatting

I have a Mysql Table that is used for a log file on the that table there is a field called 'log_date' And it stores the date in the following format( %Y-%m-%d %H:%i.%s ).On the DB the dates look like something this 2013-20-05 00:00.00. Lets say today's date is 2013-20-05 And I have log files from 2013-01-01 to present day. If I run a query like this:

SELECT * FROM log_table
WHERE STR_TO_DATE(log_date, '%Y-%m-%d %H:%i.%s') < '2013-05-05 00:00.00'

This is returning every row in the DB including rows that are greater than 2013-05-05 00:00.00

And if I reverse the < (less then) to a > (greater then) with a query that looks like this:

SELECT * FROM log_table
WHERE STR_TO_DATE(log_date, '%Y-%m-%d %H:%i.%s') > '2013-05-05 00:00.00'

Then it returns ZERO rows. I think the time stamp is what is causing the problem I have worked with the date format before but not the DateTime format. Why is this happening?

like image 331
Ryan Bauer Avatar asked May 20 '13 19:05

Ryan Bauer


People also ask

What is the correct datetime format for MySQL?

MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

How can get date in dd mm yyyy format in MySQL?

The following is the query to format the date to YYYY-MM-DD. mysql> select str_to_date(LoginDate,'%d. %m.

What is the format of datetime?

For example, the "d" standard format string indicates that a date and time value is to be displayed using a short date pattern. For the invariant culture, this pattern is "MM/dd/yyyy". For the fr-FR culture, it is "dd/MM/yyyy". For the ja-JP culture, it is "yyyy/MM/dd".

Can we change date format in MySQL?

MySQL uses yyyy-mm-dd format for storing a date value. This format is fixed and it is not possible to change it. For example, you may prefer to use mm-dd-yyyy format but you can't. Instead, you follow the standard date format and use the DATE_FORMAT function to format the date the way you want.


1 Answers

log_date should be of DateTime data type. It is much simpler to use MySQL DATE function. Some examples

SELECT * FROM log_table
WHERE DATE(log_date) < '2013-05-05'

SELECT * FROM log_table
WHERE DATE(log_date) > '2013-05-05'

SELECT * FROM log_table
WHERE DATE(log_date) BETWEEN '2013-04-05' AND '2013-05-05'

SELECT * FROM log_table
WHERE DATE(log_date) BETWEEN DATE(CURRENT_DATE() - INTERVAL 2 WEEK) AND 
DATE(CURRENT_DATE() + INTERVAL 4 DAY)
like image 92
blotto Avatar answered Oct 25 '22 20:10

blotto