在Postgres中，如何从给定的TIMESTAMP WITH TIME ZONE列中提取月份（根据特定时区）？

I have a column called login_timestamp, which is of type TIMESTAMP WITH TIME ZONE.

我有一个名为login_timestamp的列,其类型为TIMESTAMP WITH TIME ZONE。

To retrieve the month for this timestamp, I would do: EXTRACT(MONTH FROM login_timestamp).

要检索此时间戳的月份,我会这样做:EXTRACT(来自login_timestamp的月份)。

However, I would like to retrieve the month for a specific time zone (in my case, Pakistan), but can't figure out how to do that.

但是,我想检索特定时区的月份(在我的情况下,巴基斯坦),但无法弄清楚如何做到这一点。

3 个解决方案

#1

Documentation for this is under Date/Time Functions and Operators. Search that page for "at time zone".

此文档位于日期/时间函数和运算符下。在该页面搜索“at time zone”。

select extract(month from login_timestamp at time zone 'Asia/Karachi');

You can change the time zone for a single session or for a single transaction with set session... or set local.... For example, this changes the time zone for the current session.

您可以使用set session更改单个会话或单个事务的时区...或设置local ....例如,这会更改当前会话的时区。

set session time zone 'Asia/Karachi';

#2

Use the AT TIME ZONE construct:

使用AT TIME ZONE构造:

SELECT EXTRACT(MONTH FROM login_timestamp AT TIME ZONE '-5');

-5 is the constant offset for Pakistan.

-5是巴基斯坦的常数偏移量。

Details:

Ignoring timezones altogether in Rails and PostgreSQL

在Rails和PostgreSQL中完全忽略时区

#3

Try applying AT TIME ZONE. Demo

尝试应用AT TIME ZONE。演示

select extract(month from cast ('2017-07-01 01:00+03' as TIMESTAMP WITH TIME ZONE) AT TIME ZONE '+08') as monthNo

returns

    monthno
1   6

#1