MySQL中的timestampdiff（）是否等同于SQL Server中的datediff（）？

I am working on migrating functions from SQL Server 2000 to MySQL.

我正在努力将功能从SQL Server 2000迁移到MySQL。

The following statement executed in SQL Server 2000, gives the output as 109.

在SQL Server 2000中执行以下语句,输出为109。

SELECT DATEDIFF(wk,'2012-09-01','2014-10-01') AS NoOfWeekends1

The equivalent query of in mysql uses timestampdiff() instead of datediff and gives the output as 108.

在mysql中的等效查询使用timestampdiff()而不是datediff,并将输出提供为108。

SELECT TIMESTAMPDIFF(WEEK, '2012-09-01', '2014-10-01') AS NoOfWeekends1

I need the output to match when executed in MySQL, so it returns 109.

我需要输出在MySQL中执行时匹配,因此返回109。

1 个解决方案

#1

I think this could be caused by one of 2 things:

我认为这可能是由两件事之一引起的:

What is classified as the first day of the week between your SQL Server and MySQL instances.

什么被归类为SQL Server和MySQL实例之间的一周的第一天。

How weeks are counted between SQL Server and MySQL

SQL Server和MySQL之间的计算周数

Your given date 2012-09-01 falls on a Saturday, which seems to rule out the start day of the week, which is usually Sunday or Monday.

您的特定日期2012-09-01属于星期六,这似乎排除了本周的开始日期,通常是星期日或星期一。

MySQL has a default start day of: 0 (Sunday)

MySQL的默认开始日期为:0(星期日)

To find out your SQL Server start of the week you can use @@DATEFIRST by running this:

要查找本周的SQL Server开始,您可以通过运行以下命令来使用@@ DATEFIRST:

select @@DATEFIRST -- default US English = 7 (Sunday)

You could change your calculation to work on days rather than weeks and dividing by 7 to get a more accurate value, which you can round as you please:

您可以将计算更改为在几天而不是几周工作并除以7以获得更准确的值,您可以随意舍入:

MySQL: SQL Fiddle Demo

MySQL:SQL小提琴演示

SELECT TIMESTAMPDIFF(DAY, '2012-09-01', '2014-10-01')/7 AS NoOfWeekends1


| NOOFWEEKENDS1 |
|---------------|
|      108.5714 |

SQL Server: SQL Fiddle Demo:

SQL Server:SQL小提琴演示:

SELECT DATEDIFF(d,'2012-09-01','2014-10-01')/7.0 AS NoOfWeekends1

| NOOFWEEKENDS1 |
|---------------|
|    108.571428 |

You could round that up or down depending on if you want to match your previous result or count it as an extra weekend.

您可以向上或向下舍入,具体取决于您是否要匹配以前的结果或将其计为额外周末。

SQL Server seems to count the number of Sundays (if that's the start of the week) between 2 dates as shown with this example fiddle where I've changed the date range to be 2 days, a Saturday and a Sunday:

SQL Server似乎计算了2个日期之间的星期日数(如果这是本周的开始),如此示例小提琴所示,我将日期范围更改为2天,星期六和星期日:

SELECT DATEDIFF(wk,'2012-09-01','2012-09-02') AS NoOfWeekends1

| NOOFWEEKENDS1 |
|---------------|
|             1 |

Where as the same values in MySQL seems to only count a full 7 days as a week as shown in this demo fiddle:

MySQL中的相同值似乎只计算一周的整整7天,如此演示小提琴所示:

SELECT TIMESTAMPDIFF(WEEK, '2012-09-01', '2012-09-02') AS NoOfWeekends1

| NOOFWEEKENDS1 |
|---------------|
|             0 |

It's only when a full 7 days pass you get the result of 1 as you can see in this demo fiddle:

只有在完整的7天过去后,你才会得到1的结果,你可以在这个演示小提琴中看到:

SELECT TIMESTAMPDIFF(WEEK, '2012-09-01', '2012-09-08') AS NoOfWeekends1

| NOOFWEEKENDS1 |
|---------------|
|             1 |

#1