node.js PostgreSQL CURRENT_TIMESTAMPでは正しく時間を挿入できない

Question

前提・実現したいこと

下のソースコードで実行しようとすると以下のエラーが出ました。

発生している問題・エラーメッセージ

error: invalid input syntax for type timestamp: "CURRENT_TIMESTAMP"
    at Parser.parseErrorMessage (/home/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/home/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/home//node_modules/pg-protocol/dist/parser.js:39:38)
    at TLSSocket.stream.on (/home/node_modules/pg-protocol/dist/index.js:11:42)
    at TLSSocket.emit (events.js:198:13)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at TLSSocket.Readable.push (_stream_readable.js:224:10)
    at TLSWrap.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
  length: 126,
  name: 'error',
  severity: 'ERROR',
  code: '22007',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'datetime.c',
  line: '3755',
  routine: 'DateTimeParseError' }

該当のソースコード

require('pg').defaults.ssl = true;
const { Client } = require('pg')
//const Sequelize = require('sequelize');
//const sequelize = new Sequelize('postgres://...');
 
const pg = new Client({
    user: '',
    host: '',
    database: '',
    password: '',
    port: ,
    
    
})

 
const sql = "INSERT INTO sensor (id,time,place,cong) VALUES ($1, $2, $3, $4)"
const values = [5, "CURRENT_TIMESTAMP", 3, 15]
 
pg.connect()
.then(() => console.log("接続完了"))
.then(() => pg.query(sql, values))
.then(result => console.log(result))
.catch((err => console.log(err)))
.finally((() => pg.end()))

試したこと

"CURRENT_TIMESTAMP"を"now"に直したらUTC時間ですが、データ挿入できました。
しかし、日本時間を挿入したいです。

CREATE TABLEの際は

time timestamp

という風に定義しました。

ご教授いただけたら助かります。

Answer 1

エラーになるのは"CURRENT_TIMESTAMP"という文字列を入力しようとしているからと捉えられているからでしょうね。

const sql = "INSERT INTO sensor (id,time,place,cong) VALUES ($1, CURRENT_TIMESTAMP, $2, $3)"
const values = [5, 3, 15]

ならどうでしょうか？

ところで、TIMESTAMP型をお使いのようですが、
TIMESTAMP WITH TIMEZONE型を使えばタイムゾーンを伴った日時の管理ができるのですが、
定義を見直してみてはいかがでしょうか。
タイムゾーンの影響を手計算することから解放されて楽になると思います。
そして、デフォルト値としてCURRENT_TIMESTAMPが入るようなカラムの定義だとさらに省略できて良いでしょうね。

19.11.2. ロケールと書式設定や
8.5.3. 時間帯のあたりを読んでほしいのですが、
デフォルトで扱うタイムゾーンを与えておくことの他に、
現在の接続の中でタイムゾーンを一時的に変更することもできたりしますので、
常に日本時間を基本とするのであれば
postgresql.conf で

timezone = 'Asia/Tokyo'

定義しておくとか、接続中にて
SET TIME ZONE 'Asia/Tokyo';
しておけば良いかと。

【加筆】
接続中のデータベースのタイムゾーンがどうなっているかを調べるには

show timezone;

というクエリーを実行します。おそらくこれがUTCなどになっているために、
CURRENT_TIMESTAMP が日本時間とずれているのだと思われます。
参考：
PostgreSQL タイムゾーンを確認する | mebee

タイムゾーンを設定するには下記の記事も参考にしてください：
PostgreSQL - タイムゾーンをDBに設定する - Qiita

Comments

[AGLAAGLA]

丁寧なご回答ありがとうございます。
定義の部分からやり直すというのはCREATE TABLEの際に[time timestamptz]とするとnowでもAsia/Tokyo時間が挿入されるという解釈でしょうか、、？

[退会済みユーザー]

設定に基づいた「現在日時」であって、接続中のタイムゾーンの設定に依存します。
'Asia/Tokyo'タイムゾーンでNOWを使えば日本時間の現在日時が入りますね。

[AGLAAGLA]

一応
```
const sql = "INSERT INTO sensor (id,time,place,cong) VALUES ($1, CURRENT_TIMESTAMP, $2, $3)"
const values = [6, 1, 20]
```
で試してみるとちゃんと挿入はされているのですが、
実行した時間は12/16 13:01(日本時間)ですが
6 | 2021-12-16 04:01:30.405577 | 1 | 20
と挿入されていました。
時間の修正がされていないようです。。