Solved

How to convert Unixtime to GMTime in SQL Server

Posted on 2009-05-11
9
893 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

756 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