MySQL按current_timestamp选择上个月的数据 [英] MySQL Select last month data by current_timestamp

问题描述

直到今天，当我使用MySQL并需要执行带有日期/时间的操作时，我才使用带有unix时间戳的int列，但是没有问题，但是今天在阅读了一些指南之后，我决定使用"current_timestamp"来测试timestamp列默认情况下.

till today when i was working with MySQL and needed to perform actions with date/time i was using int column with unix timestamp and there was no problems, but today after reading some guides i decided to test timestamp column with "current_timestamp" by default.

所以我感兴趣的是如何按信息为"2012-09-07 00:23:30"格式的列选择上个月的数据?也许还有一些棘手的问题，从本月初开始(不是过去30天，而是从09-01 00:00:00直到今天)，什么将为我提供数据?

So i am interested how to select last month data by column where info is in "2012-09-07 00:23:30" format? And maybe there some tricky queries what will give me data from the start of this month (not last 30 days, but from 09-01 00:00:00 till today) ?

推荐答案

这将为您提供最后一个月:

This will give you the last month:

WHERE dateColumn BETWEEN SUBDATE(CURDATE(), INTERVAL 1 MONTH) AND NOW();

从月初开始:

WHERE dateColumn BETWEEN STR_TO_DATE('2012-09-01', '%Y-%m-%d') AND NOW();

BETWEEN没什么特别的，它只是它的快捷方式

The BETWEEN is nothing special, it's just a shortcut for

dateColumn <= ... AND dateColumn >= ....

嗯，我想实际上并不需要NOW()比较，因为所有记录都早于现在.

Hmm, I guess the NOW() comparison is not actually needed, since all the records will be before now.

所以就做吧

WHERE dateColumn >= STR_TO_DATE('2012-09-01', '%Y-%m-%d')

本月的动态开始:

WHERE dateColumn >= CURDATE() - INTERVAL DAY(CURDATE())-1 DAY

所有这些操作是从当前日期中提取一个月中的某天，然后从中减去少几天.

All this does is extract the day of the month from the current date, then subtract that many days less one from it.

这篇关于MySQL按current_timestamp选择上个月的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！