Select all columns with time formatting

I have many columns, some of them contains timestamp. I want to fetch all columns records but ones with date/time to be formatted:

I know I can get all columns separately but there is 20 columns so I want to avoid doing:

select 
    id, 
    -- ... 19 other columns, 
    TO_CHAR(porh_received, 'YYYY-MM-DD HH24:MI:SS')
from pick_order_hist;

I expect something like this:

select *, TO_CHAR(porh_received, 'YYYY-MM-DD HH24:MI:SS') from pick_order_hist;

but it doesn’t work.

Answer

Option 1

One solution would be to alter the NLS setting of your session, which controls the default format in which timestamps are returned, to the target timestamp format. Depending on your real datatype, this could be any of the three following:

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

Then, you can simply do:

SELECT * FROM pick_order_hist;

The downside of this approach is that this alters the default format for the lifetime of your session (or until later on changed to something else). Depending on your use case, this might, or might not be what you need.

Option 2

Else, you could fetch all columns with *, and then add the formated timestamp(s) columns, using column aliases in the resultset. So instead of listing all columns, you just need to list the timestamp columns. The downside is that you get a few extra columns in the resultset, one for each unformated timestamp; you can simply ignore them in your application.

SELECT
    t.*, 
    TO_CHAR(porh_received, 'YYYY-MM-DD HH24:MI:SS') porh_received_with_format
FROM pick_order_hist t;