Solved

How to convert Unixtime to GMTime in SQL Server

Posted on 2009-05-11
9
899 Views
Last Modified: 2012-05-06
Hi guys i am trying to get last 30 days data but its unix datetime So,how do i convert and get the data for last days on a daily basis..

Thanks
0
Comment
Question by:danny1620
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 9

Assisted Solution

by:ezraa
ezraa earned 100 total points
ID: 24358852
MyTime = DATEADD(s, UnixTime, '1970-01-01 00:00:00')
0
 
LVL 9

Expert Comment

by:Hwkranger
ID: 24358868
SELECT dateadd(s, ,<YOUR DATE TIME IN UNIX>, '19700101')
eg.
SELECT dateadd(s, 1113211532, '19700101')

returns; 2005-04-11 09:25:32.000
0
 

Author Comment

by:danny1620
ID: 24358941
Thanks Guys ...
I was using the below SQL to get the last 30 days data .. How can i use the same to get the last 30days data which has uNIX timestamp

SELECT ID Where Date >= DATEADD(D,DATEDIFF(DAY, 0 ,GETDATE())-30, 0)
and Date < DATEADD(D,DATEDIFF(DAY, 0 ,GETDATE()), 0)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:danny1620
ID: 24358960
I am sorry its the otherway .. I need to check GMT date against Unix Date by converting GMT to UNIX..
0
 
LVL 9

Expert Comment

by:ezraa
ID: 24358977
just reverse it:

DATEDIFF(s, '1970-01-01 00:00:00', UnixTime)
0
 
LVL 9

Expert Comment

by:ezraa
ID: 24358986
correction:

UnixTime  = DATEDIFF(s, '1970-01-01 00:00:00',MyTime)
0
 
LVL 9

Expert Comment

by:Hwkranger
ID: 24359049
SELECT
 *
FROM
 [YOUR TABLE]
WHERE
  Convert(datetime, dateadd(s, ,<YOUR DATE TIME COLUMN IN UNIX>, '19700101')) >= dateadd(dd, -30, getdate())
0
 
LVL 9

Accepted Solution

by:
Hwkranger earned 400 total points
ID: 24359072
Or the other way around...

SELECT *
FROM <YOUR TABLE>
WHERE
 <YOUR TABLE COLUMN IN UNIX FORMAT> >= DATEDIFF(s, '19700101', Dateadd(dd, -30,GETDATE()))
0
 

Author Closing Comment

by:danny1620
ID: 31580321
Thanks guys
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question