Convert date/time in oracle table-CodePudding

I have the following date/time in the table that I need to convert and select anything that is less than 30 days.

I have two columns I have in a table: name, timestamp

timestamp is in this format: 2022-02-08T22:19:12:200

This is the query I tried so far just to select and convert the date to different format but getting oral-01843:not a valid month

select name, to_date(timestamp, 'MM/DD/YYYY') from mytable;

CodePudding user response：

Assuming that your timestamp column is soted in a TIMESTAMP data type then you do not need to convert the column to a different format (firstly, because a TIMESTAMP is a binary data-type consisting of 7-13 bytes representing 7 bytes for century, year-of-century, month, day, hour, minute and second and then 6 optional bytes for fractional seconds and is not stored in any human-readable format; and secondly, because conversion is not necessary as you can do it without).

To find the rows that are less than exactly 30 days old, you can use:

SELECT name, timestamp
FROM   table_name
WHERE  timestamp >= SYSTIMESTAMP - INTERVAL '30' DAY;

If you want to ignore the time component then you can use:

SELECT name, timestamp
FROM   table_name
WHERE  timestamp >= TRUNC(SYSDATE) - INTERVAL '30' DAY;

If you want to display the timestamp column in a format (rather than comparing two dates/times) then you can use TO_CHAR:

SELECT name,
       TO_CHAR(timestamp, 'MM/DD/YYYY') AS formatted_timestamp
FROM   table_name

If your column is a VARCHAR2 data type and you are storing the string in ISO8601 format then you can use:

SELECT name, timestamp
FROM   table_name
WHERE  timestamp >= TO_CHAR(SYSTIMESTAMP - INTERVAL '30' DAY, 'YYYY-MM-DD"T"HH24:MI:SS.FF');

Or, if you want to ignore the time component, then just remove the time component from the format model in TO_CHAR:

SELECT name, timestamp
FROM   table_name
WHERE  timestamp >= TO_CHAR(SYSTIMESTAMP - INTERVAL '30' DAY, 'YYYY-MM-DD');

If you want to display your string in a different format then convert it to a timestamp and then back to a string:

SELECT name,
       TO_CHAR(
         TO_TIMESTAMP(timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF'),
         'MM/DD/YYYY'
       ) AS formatted_date
FROM   table_name

db<>fiddle here