locked
How to convert a datetime to epoch RRS feed

  • Question

  •  All,

           I am using a stream analytics query that is taking input from iot hub and is sending it back to a <g class="gr_ gr_100 gr-alert gr_spell gr_disable_anim_appear ContextualSpelling ins-del multiReplace" data-gr-id="100" id="100">documentdb</g>. I would like to have the <g class="gr_ gr_137 gr-alert gr_spell gr_disable_anim_appear ContextualSpelling ins-del multiReplace" data-gr-id="137" id="137">datetime</g> converted as epoch while sending it to <g class="gr_ gr_202 gr-alert gr_spell gr_disable_anim_appear ContextualSpelling ins-del multiReplace" data-gr-id="202" id="202">documentdb</g> output. What is the best way to do this?

    Regards

    <g class="gr_ gr_255 gr-alert gr_spell gr_disable_anim_appear ContextualSpelling ins-del multiReplace" data-gr-id="255" id="255">Tiklu</g>

    Wednesday, February 1, 2017 2:09 PM

Answers

  • epoch time is number of milliseconds passed since 1970. You can perhaps use DATEDIFF to compute it:

    DATEDIFF(ms, '1970-01-01T00:00:00Z', [yourTimeColumn])

    • Marked as answer by Tiklu Ganguly Monday, February 6, 2017 3:31 PM
    Wednesday, February 1, 2017 7:55 PM
  • Dear Konstantin,

                         Thanks for the reply. I have used your query as follows

     DATEDIFF(s,'1970-01-01T00:00:00Z',EventEnqueuedUtcTime) AS EnqueuedTime,

    Actually, Epoch is based on second and not millisecond so I changed the first parameter as s instead of ms. Thanks again for your reply. 

    Best Regards

    <g class="gr_ gr_11 gr-alert gr_spell gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="11" id="11">Tiklu</g>

    • Marked as answer by Tiklu Ganguly Monday, February 6, 2017 3:33 PM
    Monday, February 6, 2017 3:33 PM

All replies

  • epoch time is number of milliseconds passed since 1970. You can perhaps use DATEDIFF to compute it:

    DATEDIFF(ms, '1970-01-01T00:00:00Z', [yourTimeColumn])

    • Marked as answer by Tiklu Ganguly Monday, February 6, 2017 3:31 PM
    Wednesday, February 1, 2017 7:55 PM
  • Dear Konstantin,

                         Thanks for the reply. I have used your query as follows

     DATEDIFF(s,'1970-01-01T00:00:00Z',EventEnqueuedUtcTime) AS EnqueuedTime,

    Actually, Epoch is based on second and not millisecond so I changed the first parameter as s instead of ms. Thanks again for your reply. 

    Best Regards

    <g class="gr_ gr_11 gr-alert gr_spell gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="11" id="11">Tiklu</g>

    • Marked as answer by Tiklu Ganguly Monday, February 6, 2017 3:33 PM
    Monday, February 6, 2017 3:33 PM