How to Get This Timestamp in the Format I Want, Oracle SQL

How do I get this timestamp in the format I want, Oracle SQL

As @Gordon said, timestamps (and dates) are not stored in a format you would recognise Oracle uses an internal representation that you never really need to know about or examine (but it is documented if you're interested in that sort of thing).

When you query a timestamp it is displayed using your client's NLS settings, unless you have a client that overrides those. I can set my session up to match what you are seeing:

alter session set nls_timestamp_format = 'DD-MON-RR HH.MI.SS.FF AM';

select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS') from dual;

TO_CHAR(SYSTIMESTAM
-------------------
2018-07-10 15:37:31

select to_timestamp(to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') from dual;

TO_TIMESTAMP(TO_CHAR(SYSTIMESTA
-------------------------------
10-JUL-18 03.37.31.000000000 PM

And I can change it see what you want to see:

alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS';

select to_timestamp(to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') from dual;

TO_TIMESTAMP(TO_CHA
-------------------
2018-07-10 15:37:32

But all you are doing is converting from a timestamp with time zone (which is what systimestamp is) to a string and then back to a timestamp. You are losing the time zone portion, and any fractional seconds; which you could also do with a cast:

select cast(systimestamp as timestamp(0)) from dual;

CAST(SYSTIMESTAMPAS
-------------------
2018-07-10 15:37:32

You can see the timezone and fraction seconds with your default timestamp_tz format:

select systimestamp from dual;

SYSTIMESTAMP                        
------------------------------------
2018-07-10 15:37:33.776469000 +01:00

and change it with a different alter:

alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM';

select systimestamp from dual;

SYSTIMESTAMP                  
------------------------------
2018-07-10 15:37:34.070 +01:00

Which isn't entirely relevant if you're really talking about storing timestamps in a table, but shows that there are variations.

In your table make the data type timestamp (or timestamp with time zone or timestamp with local time zone), and only worry about formatting the value as a string for presentation to the end user, at the last possible moment.

When you do need to display it, if the display format is important to you then use to_char() with an explicit format mask - do not assume that anyone else running your queries will have the same NLS settings. As you can see, it's easy to change those to modify the output. (Most clients have a way to let you set the defaults so you don't have to do the same alter commands every time you connect; e.g. in SQL Developer, from Tools->Preferences->Database->NLS). If you want to always show the same format then use something like:

select to_char(your_column, 'YYYY-MM-DD HH24:MI:SS') as column_alias
from your_table
where your_column < timestamp '2018-01-01 00:00:00'

which also shows the column value being filtered (as a timestamp still) using a timestamp literal.

Change timestamp format in oracle

A TIMESTAMP is a binary data format consisting of 7-20 bytes (century, year-of-century, month, day, hour, minute, second, up to 6 bytes for fractional seconds and up to 7 bytes for time zone information); it does NOT have a format.

Why am I seeing the TIMESTAMP with a format?

You are seeing it with a format because whatever user interface you are using to access the database has decided that it is more useful to display the binary information as a formatted string rather than returning the raw byte values to you.

Typically, for SQL/Plus and SQL Developer, this is managed by the NLS_TIMESTAMP_FORMAT session parameter. Other user interfaces will have different mechanisms by which they manage the default format of dates and timestamps.

If you want to change the default for SQL/Plus (and SQl Developer) then you can use:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9';

(Or whatever format you would like.)

How can I format the TIMESTAMP value?

If you then want to display the timestamp with a format (remember, a TIMESTAMP is not stored with any format) then you want to use TO_CHAR to convert it to a string where it can have a format.

If you want to format the TIMESTAMP as a YYYY-MM-DD HH24:MI:SS.FF3 string then use:

SELECT TO_CHAR( your_timestamp_column, 'YYYY-MM-DD HH24:MI:SS.FF3' )
FROM   your_table

How can I convert a formatted string back to a TIMESTAMP?

From your comment:

I loaded the oracle data into a different SQL table where date format was varchar and data got loaded as '01-APR-21 12.02.00.496677000 AM'

Use TO_TIMESTAMP:

SELECT TO_TIMESTAMP( your_string_column, 'DD-MON-RR HH12:MI:SS.FF9 AM' )
FROM   your_table

(Note: that this will convert your string to a binary TIMESTAMP and then whatever user interface you are using will use its rules on how to display it; and if the default format it is using is DD-MON-RR HH12:MI:SS.FF9 AM then the query above will look like it has done nothing; it has, the UI is just implicitly converting it back to a string to display it.)

Formatting timestamp in oracle

This is a pretty terrible data model. A timestamp should really, really be stored in a timestamp column rather than in a varchar2. Both because then the data type identifies what is actually in the column and because it is more efficient and because it lets you use all Oracle's timestamp functions on the data sensibly.

Assuming that you are stuck with an incorrect data model

update your_table
   set your_column = to_char( to_timestamp( your_column, 'YYYY-MM-DD:HH24:MI:SS.FF9' ),
                              'MM/DD/YYYY HH:MI:SS.FF3 PM' )

would update all the data to the new format in the unlikely event that every single value in the table is in the correct format already. In most real-world systems, you'd need to do a fair amount of clean-up first because inevitably someone has stored in incorrect string or two in your column.

If you do happen to be able to update all the data successfully, be aware that any queries that do order by your_column will almost certainly stop doing what you want. Since the column is a varchar2 rather than a timestamp, sorting is done alphabetically rather than by the point in time that the string represents. If you change the format to something where temporal order doesn't match alphabetical order, you are likely to have unhappy users.

Convert timestamp to date in Oracle SQL

CAST(timestamp_expression AS DATE)

For example, The query is : SELECT CAST(SYSTIMESTAMP AS DATE) FROM dual;

Oracle SQL developer timestamp format not changing to 24

if I use ("%d-%b-%Y %H:%M:%S") in which the 24h format is %H I'm getting an error Failed to insert record into table ORA-01849: hour must be between 1 and 12 ,

You are relying on implicit conversion of your string to a timestamp, which uses your session NLS settings. Note that is your session from Python, which is separate from your SQL Developer session - and they have different NLS settings.

Change your insert to expect a string with a 24-hour time; instead of the first bind being simply

query="... values(:1, ..."
values = (timestamp_v, ...

make it:

query="... values(to_timestamp(:1, 'DD-MON-YYYY HH24:MI:SS`), ..."
values = (timestamp_v, ...

... which still replies on the month abbreviation being converted - implying your Python and Oracle sessions are using the same date language; you can specify the language on the Oracle side but... it would be better to use an unambiguous format like

timestamp_v = datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
...
... values(to_timestamp(:1, 'YYYY-MM-DD HH24:MI:SS`), ...
values = (timestamp_v, ...

But it would be even better to bind the actual timestamp value and not use a string at all. I don't know Python but perhaps something as simple as:

query="... values(:1, ..."
values = (datetime.utcnow(), ...

(This answer suggests that is the case.)

It's much better to use the correct data type rather than converting to and from strings, whether that is implicit or explicit.

if I use ("%d-%b-%Y %I:%M:%S") with %I is for the 12h format, it's getting inserted no problems.

Yes, because you're now providing the hour as 11, not 23.

note that my sql developer is always showing the 12H format

No, it's showing 24-hour format, your time is just actually 11 am.

How to specify timestamp format in Oracle SQL

Looks like you're dealing with timestamps. If that's so, have a look at this example:

Sample table:

SQL> create table test (arrvtime timestamp, deptime timestamp);

Table created.

SQL> insert into test values (systimestamp, systimestamp - 0.2);

1 row created.

SQL> select * From test;

ARRVTIME                        DEPTIME
------------------------------- -------------------------------
08.05.20 21:04:50,508000        08.05.20 16:16:50,000000

Query you might need:

SQL> select arrvtime - deptime diff,
  2         extract(hour from arrvtime - deptime) hours,
  3         extract(minute from arrvtime - deptime) minutes,
  4         --
  5         -- what you want
  6         extract(hour  from arrvtime - deptime) ||':'||
  7         extract(minute from arrvtime - deptime) result
  8  from test;

DIFF                                 HOURS    MINUTES RESULT
------------------------------- ---------- ---------- ------
+000000000 04:48:00.508000               4         48 4:48

SQL>

---

Related Topics