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

Question

I firstly run the following command, and get the following result:

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

TO_CHAR(SYSTIMESTAM
-------------------
2018-07-10 10:21:40

This is the format I want to have a TIMESTAMP object stored in.

When I convert it back though, it does not come in the format I want:

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 10.21.40.000000000 AM

In fact, it changes 2018 to be at the end, sets 07 to be "JUL" and the 10 is now at the front. Also the time is separated by dots, has many 0's and an AM.

How can I fix this? I am new to SQL development so I am not sure about the formatting.

Thank you so much

Timestamps are stored in an internal format. Use `to_char()` to convert to any string representation that you prefer. — Gordon Linoff, Jul 10 '18 at 14:27
I would like to keep the datatype in the end as a timestamp so I can parse it later if needed (give me the records from this date to that date), as an example — K Split X, Jul 10 '18 at 14:29
@KSplitX - right, and that's what Oracle's internal representation does. You only need to format it to be readable, as a string, for final display to the end user. — Alex Poole, Jul 10 '18 at 14:32
Okay so if I understand this correctly, the **datatype** should still remain TIMESTAMP, and if I ever want to parse it in the future, I can write a sql query like ... select to_char(timestamp1, format) from my_table where timestamp1 > timestamp2, and this should return whatever format I want — K Split X, Jul 10 '18 at 14:40
Why don't you configure your SQL client to _display_ timestamps that way? — , Jul 10 '18 at 14:46
http://www.orafaq.com/wiki/Timestamp << This details how Oracle stores a `timestamp` data type. It stores it as an 11 byte entity that needs to be made human-readable to make sense as a date. You don't usually want to store anything (except character data) in a database in a way that a user can read it. Otherwise just use some sort of character datatype. You want those datatypes to be usable by the server itself. So let it choose how to store data. — Shawn, Jul 10 '18 at 14:53
Instead of `to_char(timestamp1, format)` you can specify the default format also by `NLS_TIMESTAMP_TZ_FORMAT` and `NLS_TIMESTAMP_FORMAT` settings. Either as Environment variable or by `ALTER SESSION SET ...` or in Windows Registry — Wernfried Domscheit, Jul 10 '18 at 14:55
When you run a query, what you see for the results of the `SELECT` is usually the IDE's way of interpreting the display of the datatype. If you selected a date, then `123456.789000` wouldn't really be very useful to you. — Shawn, Jul 10 '18 at 14:56

Alex Poole · Accepted Answer · 2018-07-10T14:50:34.830

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.

score 3 · Answer 2 · answered Jul 10 '18 at 15:24

This is the format I want to have a TIMESTAMP object stored in.

This is a common misconception - timestamp (and date) data types do not have a format; they are stored in the database as 20 bytes (or 7 bytes for dates) representing:

year (2 bytes),
month, day, hour, minute, integer seconds (1 bytes each),
fractional seconds (4 bytes),
time zone offset hours (1 byte),
time zone offset minutes (1 byte),
other data (including time zone location) (7 bytes)

You can see the bytes using the DUMP function:

SELECT DUMP( your_timestamp_column ) FROM your_table;

The database will operate on these 20 bytes without any format. But that would not be useful to display to you, the user, so the user interface you use to access the database (SQL/Plus, SQL Developer, Toad, Java, C#, etc) will receive those raw bytes from the database and will silently format them into a more intelligible format for you, the user.

What you are actually asking is:

How can I get the application I am using to access the Oracle database to change the default format it uses to display TIMESTAMP data types?

For SQL/Plus (and SQL Developer) you can use the NLS_TIMESTAMP_FORMAT session parameter:

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

Then:

SELECT SYSTIMESTAMP FROM DUAL;

Will output:

2018-07-10 16:24:53

However, this only sets the default format for your user's current session; other users can set their own parameters and can change the value during their session so you should not rely on this to provide a consistent formatting.

Instead, if you want to have a TIMESTAMP with a particular format then you should convert it to a datatype which can have a format - a string.

SELECT TO_CHAR( SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL;

Then it does not matter what the user changes their default timestamp format to - your values will always be formatted how you expect.

score 0 · Answer 3 · edited Jul 10 '18 at 14:46

So based on your replies above it sounds like you may be trying to do something you don't actually need to do.

As Gordon mentioned, timestamps are stored in an internal format so you can do things with the values. If you have the field stored as a timestamp data type you don't need to care how it's formatted in the database, you just need to care about how it looks for your query at the end. You can display a date field using to_char and still use a date range if you have the query built correctly.

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

I don't think it is possible to to display the timestamp as you've described without using to_char and losing the data type.

`WHERE systimestamp <= current_timestamp` is pointless. It always returns TRUE. — Wernfried Domscheit, Jul 10 '18 at 14:57

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

3 Answers3

Linked

Related