如何从hsqldb中的Unix时间戳正确地替换UTC时间戳 [英] How can I get correctly displaced UTC timestamp from a unix timestamp in hsqldb

问题描述

在hsqldb中，函数TIMESTAMP()返回一个WITHTIME TIME ZONE时间戳，该时间戳在进行任何进一步转换之前已调整为会话的时区.

In hsqldb the function TIMESTAMP ( ) returns a WITHOUT TIME ZONE timestamp which is adjusted to session's time zone before any further conversion.

所以可以说我的会话是在UTC + 1上，并且我的unix时间戳为1364353339(根据格林尼治标准时间2013年3月27日星期三03:02:19，根据

So lets say my session is at UTC+1 and I have a unix timestamp of 1364353339 (Wed, 27 Mar 2013 03:02:19 GMT, according to http://www.onlineconversion.com/unix_time.htm) coming from elsewhere. If I call:

VALUES( TIMESTAMP( 1364353339 ) AT TIME ZONE INTERVAL '0:00' HOUR TO MINUTE );

给出 2013-03-27 02:02:19.000000 + 0:00 .它具有正确的tz，但实际值应比实际值少一小时.

which gives 2013-03-27 02:02:19.000000+0:00. This has the correct tz but actual value is one hour less tha it should.

我探索过的其他可能性

VALUES( TIMESTAMP( 1364353339 ) AT LOCAL ) --> 2013-03-27 03:02:19.000000+1:00
Value right, TZ wrong

VALUES CAST(TIMESTAMP(1364353339) AT TIME ZONE INTERVAL '0:00' HOUR TO MINUTE AS TIMESTAMP(0) WITH TIME ZONE); --> 2013-03-27 02:02:19+0:00
Value wrong, TZ right

VALUES CAST(TIMESTAMP(1364353339) AT LOCAL AS TIMESTAMP(0) WITH TIME ZONE) --> 2013-03-27 03:02:19+1:00
Value right, TZ wrong

所有这些都返回正确的值( 2013-03-27 03:02:19 + 0:00 或 2013-03-27 04:02:19 + 1:00)，如果会话的时区先前已切换为UTC(例如，使用 SET TIME ZONE INTERVAL'0:00'HOUR TO MINUTE ).

All of these return correct values (either 2013-03-27 03:02:19+0:00 or 2013-03-27 04:02:19+1:00) if session's time zone is previously switched to UTC (for instance with SET TIME ZONE INTERVAL '0:00' HOUR TO MINUTE).

我不能这样做，因为这是为视图字段计算的.实际时间戳记来自文本表上的字段.

I cannot do that as this is being computed for a view field. Actual timestamps are coming from a field on a text table.

推荐答案

这可能适用于所有时区:

This will probably work in all time zones:

VALUES TIMESTAMP(1364353339) + SESSION_TIMEZONE()

这些形式也是可能的:

VALUES TIMESTAMP(1364353339) + DATABASE_TIMEZONE()
VALUES TIMESTAMP(1364353339) + TIMEZONE()

DATABASE_TIMEZONE()可以与客户端-服务器设置中的会话不同.如果使用SET TIME ZONE，则TIMEZONE()可以与SESSION_TIMEZONE()不同

The DATABASE_TIMEZONE() can be different from the session in client-server setups. The TIMEZONE() can be different from SESSION_TIMEZONE() if SET TIME ZONE is used

这篇关于如何从hsqldb中的Unix时间戳正确地替换UTC时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！