TimescaleDB and Loopback4 Timestamp data type compatibility

Question

I am currently using TimescaleDB for one of my projects and I am running into some issues when using the built in TIMESTAMP data field with TIMESCALE. I have been pulling my hair out over this for weeks. When getting data via the loopback4 api, the timestamp comes out very weird. I will provide some code examples below.

This is an example of how my table schema is setup in Timescale. Notice how I am using the TIMESTAMP data type for t.

CREATE TABLE stock_historic(
    stock_id INTEGER NOT NULL,
    t TIMESTAMP NOT NULL,
    o NUMERIC NOT NULL, 
    h NUMERIC NOT NULL,
);

Below is what records appear as when traversing through the database. As you can see, the timestamp looks very human readable and contains all the elements that I would like it to.

 stock_id |          t          |   o   |   h   | 
 ---------+---------------------+-------+-------+
     2    | 2020-12-31 09:30:00 | 44.01 | 44.01 | 
     2    | 2020-12-31 09:31:00 | 44.01 | 44.01 |

Now here is where things start to get a little confusing. This is an example of the data model for the table stock_historic from loopback4. I used loopback4 to auto generate these models by discovering my TimescaleDB as a PostgreSQL database since Timescale is built on top of Postgres.

export class StockHistoric extends Entity {
  @property({
    type: 'number',
    required: true,
    scale: 0,
    id: 1,
    postgresql: {columnName: 'stock_id', dataType: 'integer', dataLength: null, dataPrecision: null, dataScale: 0, nullable: 'NO'},
  })
  stockId: number;

  @property({
    type: 'date',
    required: true,
    id: 2,
    postgresql: {columnName: 't', dataType: 'timestamp without time zone', dataLength: null, dataPrecision: null, dataScale: null, nullable: 'NO'},
  })
  t: string;

  @property({
    type: 'number',
    required: true,
    postgresql: {columnName: 'o', dataType: 'numeric', dataLength: null, dataPrecision: null, dataScale: null, nullable: 'NO'},
  })
  o: number;

This is what is and example of what is returned when calling the loopback4 api:

/stock-historics?filter[where][stock_id]=2&filter[limit]=10


{"stockId":2,"t":"2020-06-01T11:17:00.000Z","o":"35.16","h":"35.16"}

Right now I am stuck, I am not sure why the date is coming back as "2020-06-01T11:17:00.000Z".

I would like this to be easily filterable in the API call, but building a url like this is a little more complex than I would like.

One solution would be to switch the data type of TIMESTAMP in timescale to a UNIX EPOCH timestamp, but I am unsure if this data type exists as I cannot find any documentation about this. I could use a big int, but then I would lose out on all timeseries perks from Timescale. I am also unsure how this would integrate with loopback4 (perhaps by using a number instead of a date?).

I feel like it would be much easier to filter API calls by UNIX timestamp.

I also believe that timescale has some built in functions that can be run automatically at the database layer. This would include a timestamp conversion. While exploring these may provide a solution, I am unsure if this would be beneficial as I am not sure if handing these conversions at the database layer would be efficient.

I could just be massively overthinking this, as I have a tendency to do that. Either way, any help or input would be greatly appreciated with my problem.

score 0 · Answer 1 · answered Jun 09 '21 at 13:48

I think your problem is more related to how the loopback4 API is working and my guess is that it's automatically translating to your timezone even you're explicit in your query.

I'd suggest you to create a small POC and wrap it into some entity, so you can better understand the results of each timezone conversion:

export class TestTimestampTypes extends Entity {
  @property({
    type: 'date',
    required: true,
    id: 2,
    postgresql: {columnName: 't', dataType: 'timestamp without time zone', dataLength: null, dataPrecision: null, dataScale: null, nullable: 'NO'},
  })
  t: string;
 @property({
    type: 'date',
    required: true,
    id: 2,
    postgresql: {columnName: 't', dataType: 'timestamptz', dataLength: null, dataPrecision: null, dataScale: null, nullable: 'NO'},
  })
  tz: string;

Maybe it can help you to check what is the best approach to proceed.

You can also create a view or a table separately to test it.

Like, I'm GMT-3 here.


template1=# select now();
              now
-------------------------------
 2021-06-09 10:44:01.934311-03
(1 row)

template1=# select now()::timestamp;
            now
----------------------------
 2021-06-09 10:44:12.059788
(1 row)

template1=# select now()::timestamptz;
              now
-------------------------------
 2021-06-09 10:44:15.661201-03
(1 row)

Checking types:

template1=# select pg_typeof(now()) ;
        pg_typeof
--------------------------
 timestamp with time zone
(1 row)

template1=# select pg_typeof(now()::timestamp) ;
          pg_typeof
-----------------------------
 timestamp without time zone
(1 row)

TimescaleDB and Loopback4 Timestamp data type compatibility

1 Answers1