MSSQL - 从时间提取秒 [英] MSSQL - Extract second from time

问题描述

我是Microsoft SQL Server的新用户。

我有PostgreSQL的经验，我提取第二个使用这个函数。

  EXTRACT（EPOCH FROM timestamp）
  

<在Microsoft SQL Server中，我发现了这个函数 -

  DATEDIFF（second，0，timestamp）
  

如果时间少于24小时，这给我第二个。

但是当我尝试这样的查询。

  SELECT SUM（DATEDIFF（second，'0:00:00' ，'86：01：12'））
  

这给我一个错误。

 从字符串
转换日期和/或时间时转换失败 

从星期六搜索解决方案并找不到它。

有人帮助我，我怎么转换

解决方案

也许不是最好的方法，但你可以基本上将它分解为小时，分钟和秒，然后将它们全部添加。

这适用于2008 R2

  DECLARE @inputTime AS VARCHAR（10）= '86：01：12'
 
 DECLARE @timeSec AS INT = SUBSTRING ）* 3600 + SUBSTRING（@ inputTime，4,2）* 60 + SUBSTRING（@ inputTime，7,2）
 
 PRINT @timeSec 
  

您可以将它作为一个单行，嵌套在任何查询中：

  SELECT SUBSTRING（'86：01：12'，1,2）* 3600 + SUBSTRING（'86：01：12'，4,2）* 60 + SUBSTRING（'86：01： 12'，7,2）
  

正如你所看到的，它会把它当作一个String，前两个字符为小时，乘以3600（每分钟60秒*​​一小时60分钟）。接下来取分钟，乘以60（一分钟60秒），最后加上秒，这是* 1，因为这是你正在寻找的分辨率。

为了更容易使用，您可以创建一个函数，然后使用它来使你的代码不那么乱七八糟：

  CREATE FUNCTION dbo.stampSeconds（@inputTime VARCHAR（10））
 RETURNS INT 
 AS BEGIN 
 
 DECLARE @timeSec AS INT = SUBSTRING （@ inputTime，1,2）* 3600 + SUBSTRING（@ inputTime，4,2）* 60 + SUBSTRING（@ inputTime，7,2）
 
 RETURN @timeSec 
 $ b b END 
  

然后您可以使用如：

  SELECT dbo.stampSeconds（'29：07：01'）
  

I'm new to Microsoft SQL Server. And I wanted to extract second from the time datatype.

I've got experience in PostgreSQL and I extract the second the using this function.

EXTRACT(EPOCH FROM timestamp)

But in Microsoft SQL Server, I found this function -

DATEDIFF(second, 0, timestamp)

Which gives me the second if the time is less the 24 hours.

But when I try a query like this.

SELECT SUM(DATEDIFF(second, '0:00:00', '86:01:12'))

It gives me a error that.

Conversion failed when converting date and/or time from character string

Searching for a solution from Saturday and couldn't find it.

Some one help me out, how can I convert the time datatype which is greater then 24 hours to seconds.

Thanks in advance.

解决方案

Maybe not the best way, but you can basically explode it to hours, minutes and seconds, then add them all up. If I understand right what you wish to do is to convert a time format to seconds.

This works on 2008 R2

DECLARE @inputTime AS VARCHAR(10) = '86:01:12'

DECLARE @timeSec AS INT = SUBSTRING(@inputTime,1,2) * 3600 + SUBSTRING(@inputTime,4,2) * 60 + SUBSTRING(@inputTime,7,2) 

PRINT @timeSec

You can do this as a one-liner, to nest it in any of your queries:

SELECT SUBSTRING('86:01:12',1,2) * 3600 + SUBSTRING('86:01:12',4,2) * 60 + SUBSTRING('86:01:12',7,2)

As you can see it will treat it as a String, get the first two chars as hours, multiply by 3600 (60 seconds in a minute * 60 minutes in an hour). Next take the minutes and multiply them by 60 (60 seconds in a minute), and last add the seconds up, which is * 1 since that is the resolution you are looking for.

For easier use, you can create a function, and use that onwards to make your code less messy:

CREATE FUNCTION dbo.stampSeconds (@inputTime VARCHAR(10))
RETURNS INT
AS BEGIN

    DECLARE @timeSec AS INT = SUBSTRING(@inputTime,1,2) * 3600 + SUBSTRING(@inputTime,4,2) * 60 + SUBSTRING(@inputTime,7,2) 

    RETURN @timeSec

END

Which you can then use like:

SELECT dbo.stampSeconds ('29:07:01')

这篇关于MSSQL - 从时间提取秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！