time_stamp

[mohansrinivas]

Hi All,

 

Am using the below SQL query to fetch the trqansfer ticket details but Iw ant the details for the particular time period(for jan 2014 to dec 2014) how to achieve this using time stamp,

can we get the ticket details using open date and close date also,please advise.

select                 ,               

call_req.ref_num,                     act_log.action_desc

From act_log  ,                                                                                  

call_req            

where act_log.call_req_id = call_req.persid

  AND act_log.description LIKE('%Transfer Group from ''SRC'' to ''ESR_EMAAP''%')

[Gordon Preston]

Hi Mohan

 

Dates are stored in the mdb in Unix epoch timestamps, so you'll need to convert your start (01/01/2014 00:00:01) and end (12/31/2014 23:59:59) dates into those timestamp date formats. You can use this site to do this

 

Once you have these values you can add them to your query

 

AND timestamp > (start date timestamp)

AND timestamp < (end date timestamp)

 

Hope that this helps,

Gordon

[Anon Anon]

I love the activity log!  Don't you? 

 

Try this:

///////////////////

SELECT

    call_req.ref_num,

    act_log.action_desc,

    DATEADD(ss, act_log.time_stamp, '1/1/1970') "Timestamp"

FROM

    act_log,                                                                                 

    call_req           

WHERE

    act_log.call_req_id = call_req.persid

    AND act_log.description LIKE('%Transfer Group from ''SRC'' to ''ESR_EMAAP''%')

    AND DATEADD(ss, act_log.time_stamp, '1/1/1970') > '01/01/2014 00:00:00'

    AND DATEADD(ss, act_log.time_stamp, '1/1/1970') < '12/31/2014 23:59:59'

ORDER BY

    act_log.time_stamp

/////////////////////

 

This will add the time_stamp for sorting purposes.

NOTE:  If you may have to adjust the value +/- to time_stamp in seconds to offset any time zone issues.

 

Good Luck,

 

JW