TIMESTAMP Data Type

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

The TIMESTAMP data type is an extension of the DATE data type. It stores the year, month, and day of the DATE data type, plus hour, minute, and second values. (Oracle SQL Language Reference Timestamp Data Type)

Both Oracle and Snowflake TIMESTAMP data types have the same precision range (0-9) but different default values. In Oracle, the default precision value is 6 and in Snowflake is 9.

However, there is a difference in behavior when an inserted value exceeds the set precision. Oracle rounds up the exceeding decimals, while Snowflake just trims the values.

TIMESTAMP [(fractional_seconds_precision)] 

Sample Source Patterns

Timestamp in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE timestamp_table
(
	timestamp_col1 TIMESTAMP,
	timestamp_col2 TIMESTAMP(7)
);

INSERT INTO timestamp_table(timestamp_col1, timestamp_col2) VALUES (TIMESTAMP '2010-10-10 12:00:00', TIMESTAMP '2010-10-10 12:00:00');

Snowflake

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE timestamp_table
	(
		timestamp_col1 TIMESTAMP(6),
		timestamp_col2 TIMESTAMP(7)
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
	;

	INSERT INTO timestamp_table(timestamp_col1, timestamp_col2) VALUES (TIMESTAMP '2010-10-10 12:00:00', TIMESTAMP '2010-10-10 12:00:00');

Retrieving data from a Timestamp column

Oracle

Query

IN -> Oracle_02.sql

SELECT * FROM timestamp_table;

Result

|TIMESTAMP_COL1         |TIMESTAMP_COL2         |
|-----------------------|-----------------------|
|2010-10-10 12:00:00.000|2010-10-10 12:00:00.000|

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM
timestamp_table;

Result

|TIMESTAMP_COL1         |TIMESTAMP_COL2         |
|-----------------------|-----------------------|
|2010-10-10 12:00:00.000|2010-10-10 12:00:00.000|

Known Issues

No issues were found.

Related EWIs

No related EWIs.