Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Summary between TIMESTAMP and TIMESTAMP WITH TIME ZONE

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

TIMEZONE refers to the time difference between local time and prime meridian Greenwich time in England.

Beijing is East eighth District (+ 08:00), that is, Beijing time-Greenwich = 8 hours. Beijing sees the sun 8 hours earlier than Greenwich.

SELECT DBTIMEZONE,SESSIONTIMEZONE,LOCALTIMESTAMP,CURRENT_TIMESTAMP,SYSTIMESTAMP FROM DUAL

+ 0020 08purl 002018 4 raceme 17 20 flor05purl 26.1929872018According to 17 20VOO5RAPHY 26.192987 + 08VORE002018GUBG 4GUBE 17 20R05GULING 26.192983 + 08:00

Alter session set time_zone='+09:00'

SELECT DBTIMEZONE,SESSIONTIMEZONE,LOCALTIMESTAMP,CURRENT_TIMESTAMP,SYSTIMESTAMP FROM DUAL

+ 0000 09purl 002018 + 0037312018 + 0000937731 + 092018 + 0937731 + 092018 4Gram17 2020 05purl 02.937726 + 08:00

DBTIMEZONE: returns the database time zone.

DBTIMEZONE returns the value of the database time zone

SESSIONTIMEZONE: returns the current session time zone

SESSIONTIMEZONE returns the time zone of the current session

LOCALTIMESTAMP: returns the current time in timestamp format without a time zone on the session side

LOCALTIMESTAMP returns the current date and time in the session time zone in a value of data type TIMESTAMP

CURRENT_TIMESTAMP: returns the current time in timestamp format with time zone on the session end

CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of data type TIMESTAMP WITH TIME ZONE.

SYSTIMESTAMP: returns the current database time in timestamp format with time zone

SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.

Alter session set time_zone='+08:00'

CREATE TABLE test1 (ID number (2), t_timezone timestamp with timezone, t_local_zone timestamp with local timezone)

Insert into test1 values (2) systimestamp

Select * from test1

22018According to 4Universe 17 20flor09purl 03.298221 + 08VOUD 002018According to 4Univer 17 2020 09purl 03.298221

Alter session set time_zone='+09:00'

Select * from test1

22018Accord4Compact 17 20flor09purl 03.298221 + 08VO8Unix 002018Accord4According to Seventeen twenty-one movement09purl 03.298221

Summary: using the timestamp time field property TIMESTAMP WITH TIME ZONE, you can convert the time of the database server to the time of the current time zone. For example, when London time is 20180101 02:00:00, you can see that time is 20180101 09:00:00 in Beijing.

TZ_OFFSET returns the time zone offset corresponding to the argument based on the date the statement is executed.

TZ_OFFSET returns the number of hours and minutes that differ between the time zone and the 0 time zone based on the parameter values entered.

SELECT TZ_OFFSET ('Asia/Shanghai'), TZ_OFFSET (' US/Michigan'), TZ_OFFSET ('Europe/London') FROM DUAL

+ 08:00-04virtual 01purl 00

Select TZNAME from Vista timezone name-query all time_zone_name

FROM_TZ converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value.

FROM_TZ converts timestamp values and time zones to timestamps with time zone values.

SELECT FROM_TZ (TIMESTAMP'2018-04-3008), FROM_TZ (2004-308)

2018-4-30 800GUBG 00.000000000 + 08UR 00.000000000 + 08UBG 002018AUD 4GUBG 30 8GUBG 000000000-04GUBE 00.000000000-04MAX02018UBE 00.000000000 + 01:00

SELECT FROM_TZ (LOCALTIMESTAMP,'Asia/Shanghai'), FROM_TZ (LOCALTIMESTAMP,'US/Michigan'), FROM_TZ (LOCALTIMESTAMP,'Europe/London') FROM DUAL

2018-4-17 20 14 47.519347 + 08 002018 1947 + 0815 1947 + 0815 1947 + 0815 1947 + 0815 1947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947 + 081947

SELECT FROM_TZ (SYSTIMESTAMP,'Asia/Shanghai'), FROM_TZ (SYSTIMESTAMP,'US/Michigan'), FROM_TZ (SYSTIMESTAMP,'Europe/London') FROM DUAL;-- because SYSTIMESTAMP itself has a time zone, so the error ORA-00932: data type is inconsistent: should be TIMESTAMP, but get TIMESTAMP WITH TIME ZONE.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report