October 30, 2008 at 9:11 am
Does anyone know who to take a:unsure: date in unix 1220140800 and convert it to a standard date Sept 30 2008?
October 30, 2008 at 9:13 am
Is 1220140801 October 1, 2008?
October 30, 2008 at 9:24 am
Kevin (10/30/2008)
Does anyone know who to take a:unsure: date in unix 1220140800 and convert it to a standard date Sept 30 2008?
That time stamp represents 08/31/2008 🙂 It is merely an offset, in seconds, from 01/01/1970, at midnight. Therefore:
select dateadd(s, 1220140800 , '19700101')
Tommy
Follow @sqlscribeOctober 30, 2008 at 9:26 am
Did a little "googling" and found that Unix records date as the number of seconds since 1970-01-01 00:00:00 UTC (GMT). With that, 1220140800 is Saturday, 2008-08-31 00:00:00 UTC.
October 30, 2008 at 9:28 am
Thanks Tommy!
October 30, 2008 at 9:29 am
October 30, 2008 at 1:21 pm
Freaky, I just posted this yesterday
http://sqlservernewbie.blogspot.com/2008/10/how-to-convert-date-from-seconds-to.html
Apparently Unix timestamp range only from 1970 Jan 1 to 2038-01-19 at 3:14:08 AM
March 2, 2010 at 9:04 am
Sorry for the necropost, but this is the closest topic to an issue I'm having that I could find.
dateadd(s,@UnixDate,'01/01/1970') works to convert a Unix date to a Julian date, however, doesn't accommodate Daylight Savings Time so for all days that fall between 2nd Sunday in Mar and 1st Sunday in Nov, the time returned is off by an hour due to daylight wasting time... *erm cough, I mean daylight savings time.
Has anyone else had to deal with this before?
This is what I've come up with so far, but need some help accommodating for specific time change (2am is cutoff for DST):
declare @unixdate int
set @unixdate = 1110111111
/*18000 accounts for gmt offset of -5 - 18000 being 5 hrs shown in seconds
doesn't account for daylight savings time, but could with more effort (a case statement of some sort)
"18000" will eventually need replaced with a var storing GMT offset*/
select case when datepart(m,dateadd(s,@UnixDate-18000,'01/01/1970')) between 4 and 10
or (datepart(m,dateadd(s,@UnixDate-18000,'01/01/1970')) = 3
and (datepart(dw,(dateadd(s,@UnixDate-18000,'01/01/1970')))-1)/7>1)
--if March and 2nd Sunday or later, assumes SELECT @@datefirst returns 7 for Sunday (default)
or (datepart(m,dateadd(s,@UnixDate-18000,'01/01/1970')) = 11
and (datepart(dw,(dateadd(s,@UnixDate-18000,'01/01/1970')))-1)/7=0)
--if Nov and not yet 1st Sunday, assumes SELECT @@datefirst returns 7 for Sunday (default)
then dateadd(s,@UnixDate-21600,'01/01/1970')
else
dateadd(s,@UnixDate-18000,'01/01/1970')
end
March 2, 2010 at 9:36 am
Ignore code in prior post... here's snippet I've been working with to test conversion:
select datediff(ss,'01/01/1970','03/16/2010')+18000
--***********************************************--
declare @unixdate int
--set @unixdate = 1268544600 --Mar 14, 2010 1:30am
--set @unixdate = 1268548200 --Mar 14, 2010 2:30am
set @unixdate = 1268715600 --Mar 16 midnight
--set @unixdate = 1267592400 --Mar 3 midnight
select case when datepart(m,dateadd(s,@unixdate-18000,'01/01/1970')) between 4 and 10
or (datepart(m,dateadd(s,@unixdate-18000,'01/01/1970')) = 3
and (datepart(dd,(dateadd(s,@unixdate-18000,'01/01/1970')))-1)/7>1)
--if March and 2nd Sunday or later, assumes SELECT @@datefirst returns 7 for Sunday (default)
or (datepart(m,dateadd(s,@unixdate-18000,'01/01/1970')) = 11
and (datepart(dw,(dateadd(s,@unixdate-18000,'01/01/1970')))-1)/7=0)
--if Nov and not yet 1st Sunday, assumes SELECT @@datefirst returns 7 for Sunday (default)
then convert(varchar(max),dateadd(s,@unixdate-21600,'01/01/1970'))+' DST Converted'
else
convert(varchar(max),dateadd(s,@unixdate-18000,'01/01/1970'))+' DST Not Converted'
end
,datepart(m,dateadd(s,@unixdate-18000,'01/01/1970'))
,(datepart(dd,(dateadd(s,@unixdate-18000,'01/01/1970')))-1)/7
,datepart(hh,(dateadd(s,@unixdate-18000,'01/01/1970')))
March 2, 2010 at 1:05 pm
Alright... I functionified this:
CREATE FUNCTION [dbo].[ConvertUnixDate]
(
@TimeValue int
)
RETURNS datetime
AS
BEGIN
DECLARE @DateTime datetime
DECLARE @GMTOffsetInSeconds int
DECLARE @isDateDST bit
DECLARE @isCurrDateDST bit
SET @GMTOffsetInSeconds = DateDiff(ss, GetUTCDate(), GetDate())
IF DATEPART(m,GETDATE()) BETWEEN 4 AND 10
SET @isCurrDateDST = 1
ELSE
SET @isCurrDateDST = 0
IF DATEPART(m,DATEADD(s,@TimeValue+@GMTOffsetInSeconds,'01/01/1970')) BETWEEN 4 AND 10
SET @isDateDST = 1
ELSE
SET @isDateDST = 0
IF @isCurrDateDST = 1 AND @isDateDST = 0
SET @GMTOffsetInSeconds = @GMTOffsetInSeconds - 3600
IF @isCurrDateDST = 0 AND @isDateDST = 1
SET @GMTOffsetInSeconds = @GMTOffsetInSeconds + 3600
SELECT @DateTime = DATEADD(s,@TimeValue + @GMTOffsetInSeconds,'1/1/1970')
RETURN @DateTime
END
I've taken a break for the time being from trying to figure out the DST cutoff with starting at 2am 2nd Sun in Mar and ending at 2am 1st Sun in Nov, but above function will correctly convert to/from DST even if current date is DST and the date to convert is nonDST:
SELECT dbo.ConvertUnixDate (DATEDIFF(ss,'01/01/1970','05/14/2010 2:30')+14400)
+14400 to simulate the time it would've been saved into the table during DST (effectively becomes same as GMT -4)
Any help on getting the code added for cutoff in Mar and Nov would be greatly appreciate. For anyone following, sorry for the mental diarrhea in 2 prev posts. I feel like I'm onto something now.
July 3, 2018 at 4:20 am
The unix time stamp is merely the number of seconds between a particular date and the Unix Epoch,
SELECT DATEDIFF(SECOND,{d '1970-01-01'},GETDATE()) // This Will Return the unix time stamp In sql server
you can create a function for local date time to unix utc conversion using this Function to Unix Time Stamp In SQL server
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply