按日期时间过滤MYSQL格式 [英] Filter by datetime MYSQL formatting

问题描述

我有一个用于该表上的日志文件的Mysql表，有一个名为'log_date'的字段，它以下列格式存储日期（％Y-％m-％d％H：％i 。％s）。在数据库中，日期看起来像2013-20-05 00：00.00这样的东西。让我们说今天的日期是2013-20-05我有从2013-01-01到目前的日志文件。如果我运行这样的查询：

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'

这是返回数据库中的每一行，包括行大于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'

然后它返回ZERO行。我认为时间戳是导致我以前的日期格式使用的问题，而不是DateTime格式。为什么会发生这种情况？

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?

推荐答案

log_date应该是DateTime数据类型。使用MySQL DATE函数要简单得多。一些例子

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)

这篇关于按日期时间过滤MYSQL格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！