Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 930
  • Last Modified:

How to convert Unixtime to GMTime in SQL Server

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
danny1620
Asked:
danny1620
  • 3
  • 3
  • 3
2 Solutions
 
ezraaCommented:
MyTime = DATEADD(s, UnixTime, '1970-01-01 00:00:00')
0
 
HwkrangerCommented:
SELECT dateadd(s, ,<YOUR DATE TIME IN UNIX>, '19700101')
eg.
SELECT dateadd(s, 1113211532, '19700101')

returns; 2005-04-11 09:25:32.000
0
 
danny1620Author Commented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
danny1620Author Commented:
I am sorry its the otherway .. I need to check GMT date against Unix Date by converting GMT to UNIX..
0
 
ezraaCommented:
just reverse it:

DATEDIFF(s, '1970-01-01 00:00:00', UnixTime)
0
 
ezraaCommented:
correction:

UnixTime  = DATEDIFF(s, '1970-01-01 00:00:00',MyTime)
0
 
HwkrangerCommented:
SELECT
 *
FROM
 [YOUR TABLE]
WHERE
  Convert(datetime, dateadd(s, ,<YOUR DATE TIME COLUMN IN UNIX>, '19700101')) >= dateadd(dd, -30, getdate())
0
 
HwkrangerCommented:
Or the other way around...

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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now