查询MySQL表以获取时间戳为X周后的记录

[英]Querying MySQL Table for Records where Timestamp is X Weeks After Another

本文翻译自 Bilal Alizai Sadik 查看原文 2017/08/25 29 mysql/ timestamp

I have a MySQL table with two different Timestamp columns: created and lastSent. I'm looking to query the table and only retrieve records whose lastSent timestamp is a certain whole number of weeks ahead of created.

我有一个MySQL表，有两个不同的Timestamp列：created和lastSent。我正在寻找查询表，并且只检索lastSent时间戳在创建之前的某个整数周的记录。

╔════╦═════════╦══════════╗
║ ID ║ created ║ lastSent ║
╠════╬═════════╬══════════╣
║ 1  ║ Aug 18  ║ Aug 25   ║
╠════╬═════════╬══════════╣
║ 2  ║ Aug 11  ║ Aug 25   ║
╠════╬═════════╬══════════╣
║ 3  ║ Aug 19  ║ Aug 25   ║
╠════╬═════════╬══════════╣
║ 4  ║ Aug 20  ║ Aug 25   ║
╠════╬═════════╬══════════╣
║ 5  ║ Aug 3   ║ Aug 24   ║
╚════╩═════════╩══════════╝

For example, the query I'm looking for would give me records 1, 2, and 5, but not 3 and 4.

例如，我正在寻找的查询将给我记录1,2和5，但不是3和4。

Thank you so much! I hope I explained this well enough.

非常感谢！我希望我能够解释得这么好。

4 个解决方案

#1

You can also write your question as:

您也可以将您的问题写成：

Find the rows where created and lastSent have the same weekday.

查找创建的行和lastSent具有相同的工作日。

Then the answer would be obvious:

然后答案显而易见：

SELECT *
FROM mytable
WHERE WEEKDAY(created) = WEEKDAY(lastSent);

#2

Use the modulus operator to see if the number of days in the difference is a multiple of 7.

使用模数运算符查看差值中的天数是否为7的倍数。

SELECT *
FROM yourTable
WHERE DATEDIFF(created, lastsent) % 7 = 0

#3

You should be able to make use of the datediff function to get the number of weeks between the timestamps.

您应该能够使用datediff函数来获取时间戳之间的周数。

#4

seems you are looking for the rows with a diff with > 1 week

看来你正在寻找带有> 1周差异的行

select * from my_table  
where ROUND(DATEDIFF(lastSent, created)/7, 0) > 1

智能推荐

注意！

本站翻译的文章，版权归属于本站，未经许可禁止转摘，转摘请注明本文地址：http://www.silva-art.net/blog/2017/08/25/353b0451a67f1aafadce459ccbbff038.html。

猜您在找

如何在MySQL中将时间戳减去6周？ - How do I get Timestamp minus 6 weeks in MySQL? MySQL根据另一个表的时间戳选择Info - MySQL Selecting Info based on timestamp of another table 从MySQL表中选择行，其中PHP时间戳大于X - Select rows from MySQL table where PHP timestamp is older than X 如何从mysql数据库php获取单个记录的时间戳？ - How to get the timestamp of individual records from mysql databse php? MySQL如何获取时间戳

智能推荐