How to convert UTC date to CST date

SQL++
1

How to convert UTC date to CST date, for example, my date is

2017-05-02 17:20:07 UTC

I want to convert it to

2017-05-03 1:20:07 CST
2

Hi @atom_yang, TRY STR_TO_TZ();
select str_to_utc(now_str()), now_str(), str_to_tz(now_str(),“America/New_York”);
https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html

3

Hi @atom_yang

You can use the Str_to_tz function. For the argument to that function, it depends on whether you want timezone to reflect using CST in the winter and CDT in the summer or CST all year round.

For the first case you can use America/Chicago
cbq> select str_to_tz(str_to_utc(“2017-05-02 17:20:07”), “America/Chicago”) ;
{
“requestID”: “f97b605f-961c-442f-a068-da4dc988e8f7”,
“signature”: {
“$1”: “string”
},
“results”: [
{
“$1”: “2017-05-03 02:20:07”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “565.589”s”,
“executionTime”: “547.918”s”,
“resultCount”: 1,
“resultSize”: 51
}
}

In general, the TZ column here List of tz database time zones - Wikipedia should help you get the timezone you desire.

Hope this helps
Thanks
Isha

1 Like
4

Thank you all, the following N1QL works:

select str_to_tz(str_to_utc(SUBSTR("2017-05-02 17:20:07 UTC",0,19)), "Asia/Shanghai") || " CST"
5

For the same N1QL

CB 4.6.0 (Version: 4.6.0-3573 Enterprise Edition (build-3573)) and CB 5.0 DP return the incorrect date:

2017-05-02 09:20:07 CST

it should be

but the following N1QL return correct value

select str_to_tz(NOW_UTC(), "Asia/Shanghai") || " CST"

@vsr1 @geraldss

6

It don’t think there is issue. cc @isha.

For STR_TO_UTC() if argument string doesn’t have timezone it uses local timezone.

select STR_TO_UTC(‘2017-05-04T08:00:00-07:00’), STR_TO_UTC(‘2017-05-04T08:00:00’) ;

1 Like
7

ok, I understand.
the following N1QL works correct:

SELECT SUBSTR(STR_TO_TZ(STR_TO_UTC(SUBSTR("2017-05-02 17:20:07 UTC",0,19)) || "-00:00", "Asia/Shanghai"),0,19) || " CST"

Thank you.

8

Another option:

REPLACE(“2017-05-02 17:20:07 UTC”," UTC",“-00:00”);

1 Like