MySQL中的timestampdiff()是否等同于SQL Server中的datediff()? [英] Is timestampdiff() in MySQL equivalent to datediff() in SQL Server?

问题描述

我正在将功能从SQL Server 2000迁移到MySQL.

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

在SQL Server 2000中执行以下语句，输出为109.

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

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

mysql中的等效查询使用而不是datediff，输出为108.

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

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

在MySQL中执行时，我需要输出匹配，因此它返回109.

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

推荐答案

我认为这可能是由以下两种原因之一引起的:

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

• 什么是SQL Server和MySQL实例之间的第一天.
• SQL Server和MySQL之间的星期数

您给定的日期2012-09-01出现在星期六，这似乎排除了一周的开始日期，通常是星期日或星期一.

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.

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

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

要了解一周中开始的SQL Server，可以使用 通过运行以下命令来@@ DATEFIRST :

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

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

您可以将计算结果更改为工作日而不是工作周数，然后除以7以获得更准确的值，您可以根据需要取整:

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:

MySQL: SQL小提琴演示

MySQL: SQL Fiddle Demo

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


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

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似乎在计算两个日期之间的星期天数(如果是一周的开始)，如

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:

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

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

如此演示小提琴:

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

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

只有整整7天过后，您才能获得1的结果，如您在演示小提琴:

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

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

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

这篇关于MySQL中的timestampdiff()是否等同于SQL Server中的datediff()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！