#database-design #time-series #unix-timestamp #loopback4 #timescaledb
Вопрос:
В настоящее время я использую TimescaleDB для одного из своих проектов, и у меня возникают некоторые проблемы при использовании встроенного поля данных МЕТКИ ВРЕМЕНИ с МАСШТАБОМ ВРЕМЕНИ. Я уже несколько недель вырываю из-за этого волосы. При получении данных через API loopback4 метка времени получается очень странной. Ниже я приведу несколько примеров кода.
Это пример того, как моя схема таблицы настраивается в масштабе времени. Обратите внимание, как я использую тип данных МЕТКИ ВРЕМЕНИ для t.
CREATE TABLE stock_historic(
stock_id INTEGER NOT NULL,
t TIMESTAMP NOT NULL,
o NUMERIC NOT NULL,
h NUMERIC NOT NULL,
);
Ниже показано, как выглядят записи при перемещении по базе данных. Как вы можете видеть, метка времени выглядит очень удобочитаемой и содержит все элементы, которые мне бы хотелось.
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 |
Теперь вот здесь все начинает немного запутываться. Это пример модели данных для таблицы stock_historic из loopback4. Я использовал loopback4 для автоматической генерации этих моделей, обнаружив свою базу данных TimescaleDB в качестве базы данных PostgreSQL, поскольку шкала времени построена поверх 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;
Это то, что есть, и пример того, что возвращается при вызове API loopback4:
/stock-historics?filter[where][stock_id]=2amp;filter[limit]=10
{"stockId":2,"t":"2020-06-01T11:17:00.000Z","o":"35.16","h":"35.16"}
Прямо сейчас я застрял, я не уверен, почему дата возвращается как «2020-06-01T11:17:00.000 Z».
Я бы хотел, чтобы это можно было легко отфильтровать в вызове API, но создание такого URL-адреса немного сложнее, чем хотелось бы.
Одним из решений было бы переключить тип данных ВРЕМЕННОЙ МЕТКИ в масштабе времени на временную метку ЭПОХИ UNIX, но я не уверен, существует ли этот тип данных, поскольку я не могу найти никакой документации по этому поводу. Я мог бы использовать большое значение int, но тогда я потерял бы все преимущества временных рядов в масштабе времени. Я также не уверен, как это будет интегрироваться с loopback4 (возможно, используя число вместо даты?).
Я чувствую, что было бы намного проще фильтровать вызовы API по метке времени UNIX.
Я также считаю, что в timescale есть некоторые встроенные функции, которые могут автоматически запускаться на уровне базы данных. Это будет включать преобразование временных меток. Хотя изучение этого может дать решение, я не уверен, будет ли это полезно, поскольку я не уверен, что передача этих преобразований на уровне базы данных будет эффективной.
Я мог бы просто сильно переосмыслить это, так как у меня есть склонность к этому. В любом случае, я был бы очень признателен за любую помощь или вклад в решение моей проблемы.
Ответ №1:
Я думаю, что ваша проблема больше связана с тем, как работает API loopback4, и я предполагаю, что он автоматически переводится в ваш часовой пояс, даже если вы явно указали в своем запросе.
Я бы посоветовал вам создать небольшой POC и обернуть его в какую-нибудь сущность, чтобы вы могли лучше понять результаты преобразования каждого часового пояса:
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;
Возможно, это поможет вам проверить, каков наилучший подход для продолжения.
Вы также можете создать представление или таблицу отдельно, чтобы протестировать их.
Например, я здесь по Гринвичу-3.
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)
Типы проверки:
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)