0

Im trying to group a set of data using date_trunc. But after executing the data i got the grouped data in Date format (DD/MM/YY). How to get it to second using query ?

Table Sensor

----------------------------------
| sensorid | reading | timestamp  |
----------------------------------
|   1      |  100    | 1612331498 |
-----------------------------------
|   2      |  100    | 1614752263 |
-----------------------------------
|   1      |  10     | 1614752263 |
-----------------------------------

> select date_trunc('day', v.timestamp) as day,sum(reading) from sensor 
> v(timestamp,sensorid) where sensorid=1 group by  (DAY);

Output is

day                      sum
03/02/2021 12:00:00 am   100
03/03/2021 12:00:00 am   10
 
Expected result
day             sum
1612331498      100
1614752263      10

im using Npgsql C# client and cratedb.

CREATE TABLE sensor (
    sensorid int,
    reading double,
    timestamp double

);
june alex
  • 244
  • 4
  • 17
  • `date_trunc('day', v.timestamp)` will result in an error because `date_trunc()` only works with `timestamp` and `date` values - not with numbers. Your query can't return the output you have –  Mar 03 '21 at 07:30
  • query is working – june alex Mar 03 '21 at 07:37
  • 1
    Then your `timestamp` column doesn't contain what you show us or you are using a different query or you are not using PostgreSQL. https://dbfiddle.uk/?rdbms=postgres_13&fiddle=36aa8f1f12e0303f0f77e5d726e09139 (the part `from sensor v(timestamp,sensorid)` also makes no sense) –  Mar 03 '21 at 07:52
  • 1
    Please edit your question and add the create table statement. – clamp Mar 03 '21 at 09:20

0 Answers0