SQL & PL/SQL

There are different "date/time" data types in Oracle; they include DATE and TIMESTAMP WITH TIME ZONE. There are two main differences - timestamp includes fractions of a second, and DATE does not keep track of time zone while TIMESTAMP WITH TIME ZONE does. (There is also simple TIMESTAMP which, like DATE, is completely ignorant of time zone, and there is also TIMESTAMP WITH LOCAL TIME ZONE.)

In your example, clearly the incoming bit of data has both fractional seconds and a time zone stamp. Do you care about either of these? If you don't, then you can use the DATE data type. If you only care about fractional seconds but you don't care about time zone, you can use TIMESTAMP. But if you don't want to lose ANY information from the incoming bit, then you must use TIMESTAMP WITH TIME ZONE.

Did you consider this question already, and did you decide that you only need DATE? Please clarify either way.

By the way, if you only need date (so - disregard the fractional seconds and the time stamp flag, whether it be +00:00 or Z): in all cases, the date and the time-of-day are encoded in the first 19 characters. You can first truncate the string in that way, and then convert to a DATE with the TO_DATE function; the T can be taken care of in the format model. Something like this:

with
  inputs ( str ) as (
    select '2018-04-11T05:26:29.505+00:00' from dual
  )
select to_date(substr(str, 1, 19), 'yyyy-mm-dd"T"hh24:mi:ss') as oracle_dt   
                                    -- Notice the T in double quotes in the format model
from  inputs;
ORACLE_DT         
-------------------
2018-04-11 05:26:29

This will work regardless of the time zone being +00:00 or Z, since that part is discarded first anyway.

I have a Varchar2 field which represents a timestamp brought in from JSON. What is the best way for me to convert this so it is a 'readable' Oracle time? 

Sorry - but the above isn't clear at all.

A VARCHAR2 datatype in Oracle contains TEXT in the database character set. If you select that text and use client software to display it then it is 'readable'.

The various DATE/TIMESTAMP/other datatypes in Oracle store data in a proprietary binary format - not a text format. Binary format, by their nature, are NOT 'readable. Any tool you use will need to convert that binary format into whatever other format you wish to use.

An example that I have is '2018-04-11T05:26:29.505+00:00'.

As presented that example is TEXT - if you want to store text then use the VARCHAR2 datatype.

The 'text' appears to have fractional seconds and a timezone offset. There are several Oracle datatypes that store date/time related data in a BINARY format and that also store fractional seconds and timezone info.

See the Oracle docs about how to use TO_TIMESTAMP_TZ to convert strings to a format that includes those data elements.

https://docs.oracle.com/database/121/SQLRF/functions230.htm#SQLRF06143

You can also us TO_TIMESTAMP if you don't care about the timezone or TO_DATE if you don't care about timezone or fractional seconds.

Also sometimes the same field has no +00:00 but a Z. Do I need to be concerned about that? 

Yes - you need to use a format string that matches the text string you provide to one of the conversion functions.

https://docs.oracle.com/database/121/SQLRF/functions229.htm#SQLRF06142

https://docs.oracle.com/database/121/SQLRF/functions219.htm#SQLRF06132

https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212

thanks, @"mathguy"! I will give that a try! Since its 00:00 i don't think i need to worry about the time zone and i don't care about the fractional seconds....I'd actually tried something close to your example but without the double quotes and it wasn't liking the T so that's where I got stuck. 

so, along your other post, if I did want to keep timezone and fractional seconds, my column in my table should be of the type TIMESTAMP WITH TIME_ZONE? What would be the best way to then format that column so it makes sense to my users when i display it (i.e. DD-MON-YYYY HH24:MI)? (is there a function or do i just need to know how to properly format the to_char?)

If the time zone is irrelevant, but you want to keep the fractional second portion (so you need a TIMESTAMP), change TO_DATE to TO_TIMESTAMP and keep the first 23 characters, instead of the first 19. The time zone flag of +00:00 or Z will still be cut off, so it won't matter.

If you want a timestamp with time zone, you don't need a substring - you will keep the entire string. However, if the input comes with a Z at the end, you should change that to +00:00 first  - you can use the REPLACE function for that, as shown in this example:

with
  inputs ( str ) as (
    select '2018-04-11T05:26:29.505+00:00' from dual union all
    select '2018-05-22T21:30:55.404Z'      from dual
  )
select to_timestamp_tz(replace(str, 'Z', '+00:00'), 'yyyy-mm-dd"T"hh24:mi:ss.ff+tzh:tzm') as oracle_ts_tz
from   inputs;
ORACLE_TS_TZ                 
------------------------------
2018-04-11 05:26:29.505 +00:00
2018-05-22 21:30:55.404 +00:00

Your other question is, once the data is stored in the database (in an internal binary format that is not readable, as rp0428 said), how do you present it to your users in a readable format? That depends on your needs and your front-end. For example, if you personally use SQL Developer or Toad, the application has specific settings for how to display dates and timestamps, etc. - you can modify them as needed. Reporting tools (ApEx, whatever - I don't even know what ApEx is) have their own ways. You can also force the database to output a string instead of a date or timestamp, with the TO_CHAR function, but in general it is better to use the settings in the reporting tool rather than TO_CHAR.

I've already answered those questions and the Oracle docs provide ALL of the detail you need.

to_timestamp_tz was exactly what i needed.  I have several time fields and they aren't stored consistently but I think this will work for all of them.

this is what i ended up doing - the record was created at  '4/11/2018 12:26:40 AM'.  One of the fields comes over with the +00:00 time distinction...the other comes over with -5:00.  I used cast and to_timestamp_tz to get them both to show as 12:26 AM in a format that my users will understand.

-- for checked_in and checked_out

select to_char(cast(to_timestamp_tz('2018-04-11T00:26:00.000-05:00', 'yyyy-mm-dd"T"hh24:mi:ss.fftzh:tzm') as timestamp with local time zone), 'DD-MON-YYYY hh:mi:ss AM') x
  from dual -- 11-APR-2018 12:26:00 AM

-- event_time

select to_char(cast(to_timestamp_tz('2018-04-11T05:26:29.505+00:00', 'yyyy-mm-dd"T"hh24:mi:ss.fftzh:tzm')  as timestamp with local time zone), 'DD-MON-YYYY hh:mi:ss AM') x
  from dual  -- 11-APR-2018 12:26:29 AM 

thanks!

Janel

Where are you handling the Z for time zone? Or was that a red herring?

Well @"mathguy", after looking at the data I had originally thought I wouldn't have to deal with it since it was only showing for specific events that I'm not looking at...but now I realize it is showing in another column that I do have to use...but it looks like it is still formatting OK even on the Z records. 

Is there something I have to do to account for Daylight Savings Time?  I'm in CST.

I'm trying to verify the sql I have so far and I see that when I look at records from January they seem to be off an hour, while dates in April are formatting correctly.

Here is what I'm seeing, hopefully the tabs align ok

event_time is the column I need to format

event time formatted is what it formatted to

Record Creation Date is the time the record was created, so the formatted times should be somewhat close to this value

-- before Daylight Savings Time...times should be within the same hour as record creation date
event_time                         Event Time Formatted             Record Creation Date 
2018-01-02T23:08:00.000-06:00      03-JAN-2018 12:08:00 AM          1/2/2018 11:09:40 PM
2018-01-02T21:09:00.000-06:00      02-JAN-2018 10:09:00 PM          1/2/2018 9:10:06 PM
2018-01-02T23:38:00.000-06:00      03-JAN-2018 12:38:00 AM          1/2/2018 11:39:34 PM
2018-01-02T12:00:13.126Z           02-JAN-2018 07:00:13 AM          1/2/2018 6:00:30 AM

Events after daylight savings time look ok:

-- After Daylight Savings Time
event_time                         Event Time Formatted        Record Creation Date
2018-04-01T01:32:00.000-05:00      01-APR-2018 01:32:00 AM     4/1/2018 1:32:54 AM
2018-04-01T02:10:00.000-05:00      01-APR-2018 02:10:00 AM     4/1/2018 2:12:06 AM
2018-04-01T02:39:00.000-05:00      01-APR-2018 02:39:00 AM     4/1/2018 2:40:10 AM
2018-04-01T05:00:09.237Z           01-APR-2018 12:00:09 AM     4/1/2018 12:00:18 AM

Here is how I'm doing the call...both ptw.as_checked_in and ptw.as_checked_out have either -5:00 or -6:00 at the end of the string or else the 'Z' and are varchars

creation_date is an oracle date field.

           , decode(substr(ptw.event, instr(ptw.event, '.', -1) + 1)
                  , 'checkin' , ptw.as_checked_in
                  , 'checkout', ptw.as_checked_out
                  ) event_time
          , to_char(cast(to_timestamp_tz(
                                           decode(substr(ptw.event, instr(ptw.event, '.', -1) + 1)
                                                , 'checkin' , replace(ptw.as_checked_in, 'Z', '+00:00')
                                                , 'checkout', replace(ptw.as_checked_out, 'Z', '+00:00')
                                                )
                          , 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM') as timestamp with local time zone)
                   , 'DD-MON-YYYY HH:MI:SS AM') event_time_formatted
          , ptw.creation_date

select dbtimezone from dual returns -5:00

thanks,

Janel

Ha! Interesting, I didn't know this (it may very well be in the documentation, I didn't check). If a timezone is followed by the letter Z, then TO_TIMESTAMP_TZ treats it as if it was +00:00. And this is not by accident - if I change Z to some other letter, like N, I get an error. Good to know!

Oracle, in the vast majority of cases, deals correctly with daylight saving time; the first question I would ask is, how sure are you that the incoming data is accurate. It depends a lot on "where it comes from" - some sources are 100% accurate as they convert to UTC, others are not.

Good to know....so I don't necessarily need the Replace statement?

The data is coming from a 3rd party software that we feed into our Oracle database.  About the only way I have to verify it is to look at what the software is displaying for the records in question.

For the 3 January dates, it is as follows:

2018-01-02T23:08:00.000-06:00  Time displayed in Software - 23:08

2018-01-02T21:09:00.000-06:00  Time displayed in Software - 21:09

2018-01-02T23:38:00.000-06:00  Time displayed in Software - 23:38

2018-01-02T12:00:13.126Z          Time displayed in Software - 06:00

April date -

2018-04-01T01:32:00.000-05:00  Time displayed in Software - 01:32

2018-04-01T05:00:09.237Z          Time displayed in Software - 00:00 (time only displays out to minutes)

Thanks,

Janel

One thing to be aware of, the following special case timestamp time elements are legal in ISO 8601, but Oracle doesn't support them with their timestamp routines  :

24:00 (where 24 is only used to denote midnight at the end of a calendar day)

23:60 (where 60 denotes leap second).

I've seen both in real life data samples (XML and JSON) and it's a pain.

Correct - you don't need REPLACE, it will work without it. I thought this was what you said in Reply 10, at the end of the first paragraph, but apparently I misunderstood.

From what you show, it seems that the outside software is, indeed, aware of daylight savings time. It is very likely (but you may still want to test) that Oracle will do it correctly, too.

you're right...I had originally tested it without the replace and it worked, but then I saw your replace suggestion and thought I'd better include that, and it worked as well.

From what I can tell it's not quite getting it since the formatted January dates are off by an hour? I tried taking out the 'local' specification before time zone and then they worked beautifully but hosed up the 'Z' dates (with and without the replace)...ex 2018-01-02T12:00:13.126Z formatted to 02-JAN-2018 12:00:13 PM, and it is supposed to be 6:00 AM.

@"Paulzip" - that is just AWESOME! >:|  How on earth do you account for that?

anasazii wrote:
Paulzip - that is just AWESOME! >:| How on earth do you account for that?

I text replaced both cases as 00:00 before calling Oracle's Timestamp routines. The only sensible approach really. The only problem I had was one solitary occasion where a fine grained timing started at 23:60 and ended at 00:00, after treatment it was 0 seconds, but in reality it was 1 second.