How to alter data format and perform nested groupBy


  I have the following query:


  Which gives the following output:

  0a2ab143-2301-447a-adc6-0d155cb11c62 1484895551488
  0a68cc18-fd2f-4b3d-ae62-5bca6d3976f0 1484974850048
  0a399b49-11df-47e3-1290-9aafb31fea97 1484863176704
  0a522cb3-c1bb-422a-b1e6-d2751a3ec6bf 1484866846720
  0a07177a-7a16-4168-ba19-6dfccb957239 1484863176704

  My first question is: can I alter the format of second column -

which expresses time in milliseconds - into “yyyy-MM-ddZZ”? As far
as I understand, I have to use the following extraction function,
however, not sure how to do it. Should I use nested queries? If
yes, how?


  Suppose that I have do everything right, I expect to get the

following results:

  0a2ab143-2301-447a-adc6-0d155cb11c62 2017-01-20
  0a68cc18-fd2f-4b3d-ae62-5bca6d3976f0 2017-01-21
  0a399b49-11df-47e3-1290-9aafb31fea97 2017-01-19
  0a522cb3-c1bb-422a-b1e6-d2751a3ec6bf 2017-01-19
  0a07177a-7a16-4168-ba19-6dfccb957239 2017-01-19

  My seconds question is, suppose that I have managed to get the

above results, how can I group by second field and then count the
total number of rows? In other words, I expect to get the this:

  2017-01-20 1
  2017-01-21 1
  2017-01-19 3

  Thank you very much for your time and help.