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
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
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
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 https://en.wikipedia.org/wiki/List_of_tz_database_time_zones should help you get the timezone you desire.
Hope this helps
Thanks
Isha
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"
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"
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â) ;
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.
Another option:
REPLACE(â2017-05-02 17:20:07 UTCâ," UTC","-00:00");