MYSQL - 在日期之间检索时间戳 [英] MYSQL - Retrieve Timestamps between dates

问题描述

全部

我有一个名为timestamp的列的MYSQL表。它是 DATETIME 数据类型，具有像10/1/2009 3:25:08 PM，10/1/2009 3:30:05 PM 10/4/2009 3:40:01 PM等等。

I have a MYSQL table with a column called timestamp. It is of DATETIME datatype and has values like "10/1/2009 3:25:08 PM', "10/1/2009 3:30:05 PM', "10/4/2009 3:40:01 PM', etc..

我想编写一个SQL查询来选择时间戳字段中的所有值，两个日期..这样的东西：

I want to write a SQL query to select all the values in the timestamp field occuring between two dates.. something like this:

select timestamp from tablename where timestamp >= userStartDate and timestamp <= userEndDate

userInput日期不会有时间部分，可以建议正确的MySQL查询语法吗？
谢谢

The userInput dates will not have time portions. Can you please suggest the correct MySQL Query Syntax for this? Thanks

推荐答案

SELECT timestamp
FROM   tablename
WHERE  timestamp >= userStartDate
       AND timestamp < userEndDate + INTERVAL 1 DAY

这将选择每个记录的日期部分在 userStartDate 和 userEndDate 之间，前提是这些字段的类型为 DATE （没有时间

This will select every record having date portion between userStartDate and userEndDate, provided that these fields have type of DATE (without time portion).

如果开始和结束日期作为字符串，请使用 STR_TO_DATE 从任何给定的格式转换：

If the start and end dates come as strings, use STR_TO_DATE to convert from any given format:

SELECT timestamp
FROM   tablename
WHERE  timestamp >= STR_TO_DATE('01/11/2010', '%m/%d/%Y')
       AND timestamp < STR_TO_DATE('01/12/2010', '%m/%d/%Y') + INTERVAL 1 DAY

这篇关于MYSQL - 在日期之间检索时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！