1

I have the following query:

select point_delivery_number, bin(time,1h) as time , AVG(self_coverage) as self_coverage , AVG(generation) as generation , AVG(consumption) as consumption
from "energy_datapoints"."formatted_group_raw"
GROUP BY point_delivery_number, bin(time, 1h) 
ORDER BY time desc

The result is following:

enter image description here

I want to add an Z to the time to indicate thats an UTC time format

Result should be then:

AT..... | 2021-05-31 21:00:00.00000Z | ... | ... | ...

I tried to use CONCAT

CONCAT(bin(time,1h), 'Z') as time

It says:

line 1:31: Unexpected parameters (timestamp, varchar(1)) for function 'CONCAT'

Is there maybe an better way of doing this?

bill.gates
  • 14,145
  • 3
  • 19
  • 47

1 Answers1

0

Here is an example with the right formatting for you:

SELECT FORMAT(GetUtcDate(),'yyyy-MM-dd HH:mm:ss.ffffZ') FROM yourTable

Replace the GetUtcDate() with your time column.

  • format expects an VARCHAR, but receives an timestamp – bill.gates May 20 '22 at 09:33
  • Format can also work with datetime, not only varchar. In the example above GetUtcDate() returns a datetime and not varchar. –  May 20 '22 at 09:39