how to dateadd with bigint
in [Programming SQL]
|
Prev: Adding extra rows in a select result set
Next: ALTER TABLE statements showing up in my stored procedures
From: Dan Holmes on 11 Aug 2010 17:28 i have a list of unix timestamps that i need to convert to datetimes. 16699435200 is the largest number. SELECT DATEADD(minute, CAST(16699435200 AS BIGINT), '19700101') Based on a powershell attempt at this i don't even think a CLR function will help. PS D:\Source\5.3_trunk\Database\RMSchema\Common> (get-date '1970-01-01').addminutes(16699435200) Exception calling "AddMinutes" with "1" argument(s): "Value to add was out of range. Parameter name: value" At line:1 char:35 + (get-date '1970-01-01').addminutes <<<< (16699435200) + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException This is sql2005. In case you are wondering i am trying to import the timezone data from http://ipinfodb.com/timezonedatabase.php. thanks
From: Gert-Jan Strik on 11 Aug 2010 18:01 Dan, I don't know much at all about "unix timestamps", but in your query, you define it as the number of minutes since January 1st 1970. Now that doesn't make sense for a number like 16699435200, because that were minutes it translates into more than 31000 years. At least it will definitely not work in SQL Server, since it only supports dates up to 9999-12-31. If you know that the number is supposed to represent, let us know, and we might be able to help you. -- Gert-Jan Dan Holmes wrote: > > i have a list of unix timestamps that i need to convert to datetimes. > > 16699435200 is the largest number. > > SELECT DATEADD(minute, CAST(16699435200 AS BIGINT), '19700101') > > Based on a powershell attempt at this i don't even think a CLR function will help. > > PS D:\Source\5.3_trunk\Database\RMSchema\Common> (get-date '1970-01-01').addminutes(16699435200) > Exception calling "AddMinutes" with "1" argument(s): "Value to add was out of range. > Parameter name: value" > At line:1 char:35 > + (get-date '1970-01-01').addminutes <<<< (16699435200) > + CategoryInfo : NotSpecified: (:) [], MethodInvocationException > + FullyQualifiedErrorId : DotNetMethodException > > This is sql2005. > > In case you are wondering i am trying to import the timezone data from http://ipinfodb.com/timezonedatabase.php. > > thanks
From: Dan Holmes on 11 Aug 2010 18:25 On 8/11/2010 6:01 PM, Gert-Jan Strik wrote: > Dan, > > I don't know much at all about "unix timestamps", but in your query, you > define it as the number of minutes since January 1st 1970. Now that > doesn't make sense for a number like 16699435200, because that were > minutes it translates into more than 31000 years. At least it will > definitely not work in SQL Server, since it only supports dates up to > 9999-12-31. > > If you know that the number is supposed to represent, let us know, and > we might be able to help you. yep, i goofed. the add should have been seconds. I pulled smaller numbers from the file. This should me more manageable. --works SELECT dateadd(second, 2140689600, '19700101') --returns 2037-11-01 12:00:00.000 --fails reaches the limit of 32 bit signed int. SELECT dateadd(second, 2152184400, '19700101') how can i calc this one? And then for this one 16699435200? As seconds since 19700101 not minutes since then. thanks again.
From: Gert-Jan Strik on 11 Aug 2010 18:41 This should work, see below. It splits the large number in a days part and a seconds part. For the days part it relies on integer division (as in 3 / 2 = 1 instead of 1.5). Declare @unix_date bigint Set @unix_date = 2152184400 Set @unix_date = 16699435200 SELECT dateadd(day, @unix_date/(24*60*60), dateadd(second, @unix_date % (24*60*60), '19700101')) -- Gert-Jan Dan Holmes wrote: > > On 8/11/2010 6:01 PM, Gert-Jan Strik wrote: > > Dan, > > > > I don't know much at all about "unix timestamps", but in your query, you > > define it as the number of minutes since January 1st 1970. Now that > > doesn't make sense for a number like 16699435200, because that were > > minutes it translates into more than 31000 years. At least it will > > definitely not work in SQL Server, since it only supports dates up to > > 9999-12-31. > > > > If you know that the number is supposed to represent, let us know, and > > we might be able to help you. > > yep, i goofed. the add should have been seconds. > > I pulled smaller numbers from the file. This should me more manageable. > > --works > SELECT dateadd(second, 2140689600, '19700101') > --returns 2037-11-01 12:00:00.000 > > --fails reaches the limit of 32 bit signed int. > SELECT dateadd(second, 2152184400, '19700101') > > how can i calc this one? And then for this one 16699435200? As seconds since 19700101 not minutes since then. > > thanks again.
From: Iain Sharp on 13 Aug 2010 08:49
On Wed, 11 Aug 2010 18:25:01 -0400, Dan Holmes <dan.holmes(a)routematch.com> wrote: >On 8/11/2010 6:01 PM, Gert-Jan Strik wrote: >> Dan, >> >> I don't know much at all about "unix timestamps", but in your query, you >> define it as the number of minutes since January 1st 1970. Now that >> doesn't make sense for a number like 16699435200, because that were >> minutes it translates into more than 31000 years. At least it will >> definitely not work in SQL Server, since it only supports dates up to >> 9999-12-31. >> >> If you know that the number is supposed to represent, let us know, and >> we might be able to help you. > >yep, i goofed. the add should have been seconds. > >I pulled smaller numbers from the file. This should me more manageable. > >--works >SELECT dateadd(second, 2140689600, '19700101') >--returns 2037-11-01 12:00:00.000 > > >--fails reaches the limit of 32 bit signed int. >SELECT dateadd(second, 2152184400, '19700101') > >how can i calc this one? And then for this one 16699435200? As seconds since 19700101 not minutes since then. > >thanks again. Build in your own offset. Work out the 'unix timestamp' value of '2005-01-01 00:00:00' and subtract it from the retrieved number. declare @offset int set @offset = datediff(second,'19700101','20050101') (maybe +1) select dateadd(second,16699435200-(a)offset,'20050101') repeat with different offsets until your range of dates fits... Alternatively, if the seconds are unimportant to you select dateadd(minute,round(16699435200/60,0),'19700101') Iain |