The Oracle TO_TIMESTAMP_TZ function can be confusing, but we’ll explain it in this video.
This function allows you to convert a string value into a TIMESTAMP WITH TIME ZONE value.
A TIMESTAMP WITH TIME ZONE value is a type of DATE, but has a few components:
- A date, including day, month, and year
- A time, including hours, minutes, and seconds
- A time zone, which is the number of hours and minutes difference from GMT
This is a useful data type because it allows you to store dates in different time zones, and allow users in other time zones to see the data in a way that makes sense to them.
The time zone part is the difference from GMT. This means that a “+7:00” is 7 hours ahead of GMT (e.g. Bangkok) and “-5:00” is 5 hours behind GMT (e.g. US Eastern Standard Time).
The syntax of the TO_TIMESTAMP_TZ function is:
TO_TIMESTAMP_TZ ( input_string [, format_mask] [, nls_param] )
The input_string parameter is the string you want to convert.
The format_mask is where you specify the format of the input_string parameter. This is so you can specify the input_string in a variety of formats, and just use this format_mask to indicate which format it is in.
The nls_param parameter is another optional parameter, and allows you to specify the returning language of the month and day names.
For more information on the Oracle TO_TIMESTAMP_TZ function, including the SQL used in this video, read the related article here: