CODEWITHSUNDEEP
sqloracle-databasedatetimezonetimestamp
Fadhlie Ikram
Fadhlie Ikram

Reputation: 119

Comparing Datetime in Oracle

Do I need to convert TIMESTAMP to DATE before doing a comparison? EX:

AND SORD.CREATED >= TO_DATE(FROM_TZ(CAST(TO_DATE('','DD-MON-YYYY HH24:MI:SS') AS TIMESTAMP), 'Asia/Kuala_Lumpur') AT TIME ZONE 'UTC','DD-MON-YYYY HH24:MI:SS')
AND SORD.CREATED < TO_DATE(FROM_TZ(CAST(TO_DATE('','DD-MON-YYYY HH24:MI:SS') AS TIMESTAMP), 'Asia/Kuala_Lumpur') AT TIME ZONE 'UTC','DD-MON-YYYY HH24:MI:SS')

Just looking for an opinion, since I'm still new and learning about databases.

Upvotes: 0

Views: 3748

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241555

It's hard to tell exactly what you database you are using. From the functions in the code you provided, I am guessing Oracle? Please confirm.

In general, I can tell you the following:

  • You aren't passing any values. The first parameter to TO_DATE is empty, and that would contain the string to convert to a date.

  • You need to know what the values in your database are. Is SORD.CREATED a DATE or TIMESTAMP datatype? Does it keep UTC values? If so, then yes, you should convert local times to UTC before querying. If possible, you should do this outside of the query itself.

  • If the values in your database are of type TIMESTAMP WITH TIME ZONE, then you can use a DATE or TIMESTAMP with the AT TIME ZONE function, like you showed in your question.

  • Don't cast to/from a string unnecessarily. If your source data is already in a DATE or TIMESTAMP datatype, there's no need to convert to a string. Once a date is a date, you should keep it a date. Introducing string formatting can sometimes introduce errors.

Upvotes: 1

Related Questions