I need a list of all contacts which have logged in from 15th June 2015 to 31th March 2016 .Please help . How can I achieve this?
Please refer below link which will assist you in converting the epoch time in XLs. Only thing you have copy the output from DB to XLs and then using below link you can get desired output.
Excel 2013 Quick Tip: Convert Unix (Epoch) time in Excel - YouTube
You can run Select query against Session_log table in Database, it will list the contact IDs which has logged between given dates. And refer ca_contact table for contact ID mapped with user.
You may to create select join query to get the list.
Below is query:
From Session_log INNER JOIN ca_contact ON session_log.contact=ca_contact.contact_UUID where login_time BETWEEN '1434326400' AND '1459382400'
You can create more refine query to get the data in Human readable Date format.
Let us know if it helps.
I am trying the below query
select ca_contact.userid,session_log.* from session_log FULL JOIN ca_contactonsession_log.contact=ca_contact.contact_uuid where (date condition )
but unable to write date condition as time is mentioned as numbers in session_log table .So please explain the mechanism for time calculation.
In your query you have also mentioned date as numbers .Please explain date conversion mechanism .
there is a function in MDB database to convert it. It's called secs2date(number)
select ca_contact.userid,session_log.* from session_log FULL JOIN ca_contact on session_log.contact=ca_contact.contact_uuid where login_time BETWEEN '1483209000' AND '1485714600'
Trying this query but it is taking to much time(showing Dubbing) but after 45 mins I have cancelled it as it is not displaying anything .
Trying this query but it is taking to much time(showing Dubbing) but after 45 mins I have cancelled it as it is not displaying anything.Please suggest.
Thank you Anil for your support.
Could you confirm if the solution provided by Anil helped you resolve the issue?.
Yes it helps me a lot.
And since SQL Server 2014 (or maybe 2008, don'T remember), there is a built-in way to apply timezone to an epoch.
datetimeoffset (Transact-SQL) | Microsoft Docs
Here something that might work...
-- change -18000 to the appropriate time zone
select c.last_name, c.first_name, DateAdd(ss,(log.login_time) -18000,'19700101') as [Last Login]from ca_contact cjoin session_log log on log.contact = c.contact_uuid where DateAdd(ss,(log.login_time) -18000,'19700101') >= '2015-06-15' and DateAdd(ss,(log.login_time) -18000,'19700101') <= '2016-03-31'order by DateAdd(ss,(log.login_time) -18000,'19700101')
Now also facing same issue.
Retrieving data ...