将日期转换为周 [英] Convert dates into weeks

问题描述

如何将连续的日期转换成星期，星期六应该是星期结束示例:

How to convert the continuous date into weeks , saturday should be the week ending Eample:

2/27/2015       3/6/2015          3/13/2015      3/20/2015

这里我提供了更多关于我的要求的信息，以便您可以轻松理解.

here I am giving more info on my requirement so that you can understand easily.

我的数据库中有一个日期时间字段，可以说是 Date2.我需要在 mm/dd/yyyy. 中将此日期分组为周让我们举个例子，我的 date2 从 1/1/2015 开始到 20/1/2015.

I have a datetime field in my db lets say Date2. I need to group this date into weeks in mm/ dd/ yyyy. lets take an example my date2 start like 1/1/2015 to 20/1/2015.

1/1/2015-3/1/2015 this range displayed as   3/1/2015,
4/1/2015-10/1/2015 ----------------------- 10/1/2015,
11/1/2015-17/1/2015 ---------------------- 17/1/2015,
18/1/2015-24/1/2015 ---------------------  24/1/2015

推荐答案

你问的不是很清楚，所以我猜.猜错了请见谅.

It's not completely clear what you are asking, so I will guess. Please forgive if I guess wrong.

假设您有一个名为 Date2 的任意 DATETIME 列，并且您希望生成紧接在该值之前的星期日的 DATE.换句话说，您希望找出 DATETIME 发生在哪一周.

Let us say you have an arbitrary DATETIME column named Date2 , and you wish to produce the DATE of the Sunday immediately preceding that value. In other words, you wish to figure out the week in which the DATETIME occurs.

你可以这样做:

 FROM_DAYS(TO_DAYS(Date2) -MOD(TO_DAYS(Date2) -1, 7))

因此，如果您有一个包含 customer_id、Date2 和 sale 的 Sales 表，您可以像这样按周总结:

So, if you have a Sales table with customer_id, Date2, and sale, you can summarize by week like this:

SELECT FROM_DAYS(TO_DAYS(Date2) -MOD(TO_DAYS(Date) -1, 7)) week_beginning,
       COUNT(*) num_transactions,
       COUNT(DISTINCT customer_id) num_customers,
       SUM(sale) total_sales 
  FROM sales
 GROUP BY FROM_DAYS(TO_DAYS(Date2) -MOD(TO_DAYS(Date) -1, 7))

这将显示每个日历周开始的星期日的日期.如果您想要每个日历周结束的星期六，请改用它.

This will show the date of the Sunday that begins each calendar week. If you want the Saturday that ends each calendar week, use this instead.

SELECT FROM_DAYS(TO_DAYS(Date2) -MOD(TO_DAYS(Date) -1, 7))
          + INTERVAL 6 DAY week_ending,
       COUNT(*) num_transactions, ...

我在这里详细写了这个:http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/

I have written this up in detail here: http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/

你可以

这篇关于将日期转换为周的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！