Solved

How to convert Unixtime to GMTime in SQL Server

Posted on 2009-05-11
9
877 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
  • 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
 

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

864 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now