SQL Current Date/Time

Coding

SQL

SQL Date Manipulation

Current Date/Time/Timezone

NOTE: Do NOT use 2 digit years. Whilst the RR date format may have provided a handy workaround when we reached the year 2000, a similar problem will rear its head again as we reach 2050 e.g. in 2049 a DOB of 01-Jan-51 implies a DOB of 01-Jan-1951 if we use the RR mask or 01-Jan-2051 (a date in the future!) if we use YY. In 2051 a DOB of 01-Jan-51 implies a DOB of 01-Jan-2051 if we use the RR mask and the same if we use YY. i..e. given that people can live to 100 the use of RR or YY is ambiguous. If the DOB were for a cat and the data were purged every 40 years then, as long as the 38 year record for the oldest cat were not beaten, a 2 digit year might work. But it's an unnecessary risk; the cost of storing those extra 2 characters is not enough to be significant. Use a 4 digit year and don't worry about it until we get near the year 9999.

Current Date/Time/Timezone

Oracle

Change default date/time display format...

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF';

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-YYYY HH24:MS:SS.FF TZH:TZM';

The client-side NLS_LANG environment variable overrides the defaults for these variables in most cases.The default for NLS_DATE_FORMAT is derived from NLS_TERRITORY.

Oracle Timezone

System Date/Time

SELECT SYSDATE

FROM DUAL;

SELECT SYSTIMESTAMP

FROM DUAL;

Note that SYSDATE and SYSTIMESTAMP will always return the Date/Time of the database server operating system. If your database server is in a different timezone to your client then the date/time will likely not be the same as current time from your perspective.The Database Time Zone does NOT influence SYSDATE or SYSTIMESTAMP.

Current Date/Time

SELECT CURRENT_DATE

FROM DUAL;

SELECT CURRENT_TIMESTAMP

FROM DUAL;

SELECT LOCALTIMESTAMP

FROM DUAL;

SYSDATE/SYSTIMESTAMP are influenced by the value of the UNIX TZ environment variable when the databases and/or listener were started. If you change the timezone of the OS then you will definitely need to restart the Listener (and probably the database and ASM/Grid Control) in order for the database to pick up the new OS date/time. You may not need to restart the database if you are not using Automatic Listener Registration or MTS.
For Grid Infrastructure (i.e. database started with crsctl) then TZ is set in...$GRID_HOME/crs/install/s_crsconfig_<nodename>_env.txt

Oracle Listener

UNIX Date/Time Handling

Demonstration (TODO)

CREATE TABLE date_time_demo

(

demo_date DATE,

demo_ts TIMESTAMP;

demo_tstz TIMESTAMP WITH TIME ZONE;

demo_tsltz TIMESTAMP WITH LOCAL TIME ZONE;

);

Diagnostics

All Columns in your data that contain time zone data...

SELECT c.owner,

c.table_name,

c.column_name,

c.data_type

FROM dba_tab_cols c,

dba_objects o

WHERE c.data_type LIKE '%TIME ZONE'

AND c.owner=o.owner

AND c.table_name = o.object_name

AND o.object_type = 'TABLE'

ORDER BY 1,2,3,4

For further information on the data dictionary tables in this list see: 402614.1

Database Timezone...

SELECT DBTIMEZONE

FROM DUAL;

The database timezone should generally be +00:00 (or UTC). Although any value that has no DST can theoretically be used instead. Using a database timezone with DST complicates applicaton of DST patches and is, therefore, not recommended.
The Database Time Zone does NOT influence SYSDATE or SYSTIMESTAMP.

MySQL

System Date/Time

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()));

Note that UNIX_TIMESTAMP will always return the Date/Time of the database server operating system. If your database server is in a different timezone to your client then the date/time will likely not be the same as current time from your perspective.

Current Date/Time

SELECT CURRENT_TIMESTAMP;

+---------------------+| current_timestamp |+---------------------+| 2023-05-14 12:10:44 |+---------------------+

SELECT NOW();

+---------------------+| now() |+---------------------+| 2023-05-14 12:12:44 |+---------------------+

Bibliography

OracleHow to Diagnose Wrong Time ( SYSDATE and SYSTIMESTAMP) After DST Change , Server Reboot , Database Restart or Installation When Connecting to a Database on Unix (1627439.1)Timestamps & time zones - Frequently Asked Questions (Doc ID 340512.1)Time Zone Data in the Data Dictionary (SYS) and the Effects of a Time Zone File Update (Doc ID 402614.1)TSLTZ (TIMESTAMP WITH LOCAL TIME ZONE) Data and DST Updates (Doc ID 756454.1)Dates & Calendars - Frequently Asked Questions (Doc ID 227334.1)How does Oracle store the DATE datatype internally? (Doc ID 69028.1)The Priority of NLS Parameters Explained (Where To Define NLS Parameters) (Doc ID 241047.1)NLS_DATE_FORMAT and a Default Century (Doc ID 30557.1)Non-RAC- How to Disable Automatic or Dynamic Registration of the Database with the Default Listener (Doc ID 130574.1)https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#CDEHIFJAhttps://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/NLS_DATE_FORMAT.htmlhttps://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/NLS_DATE_LANGUAGE.htmlhttps://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/NLS_TERRITORY.htmlhttps://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/NLS_TIMESTAMP_FORMAT.htmlhttps://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/NLS_TIMESTAMP_TZ_FORMAT.html
MS-SQLhttps://docs.microsoft.com/en-us/sql/t-sql/functions/odbc-scalar-functions-transact-sql?view=sql-server-ver15https://docs.microsoft.com/en-us/sql/relational-databases/collations/write-international-transact-sql-statements?view=sql-server-ver15https://nocolumnname.blog/2020/11/20/uncommon-sql/https://nocolumnname.blog/2020/10/05/odc-date-time-extension-option-in-sql-server/https://stackoverflow.com/questions/33228765/how-do-i-specify-date-literal-when-writing-sql-query-from-sql-server-that-is-lin
MySQLhttps://www.w3schools.com/sql/func_mysql_curdate.asphttps://www.epochconverter.com/programming/mysqlhttps://www.w3schools.com/mysql/func_mysql_now.asp

Page updated

Google Sites

Report abuse