Date conversion to Postgresql-CodePudding

How to convert this into Postgresql from Oracle.

timestamp := TO_CHAR(SYSDATE (TO_NUMBER(settime)/86400),'MM/DD/YYYY HH24:MI:SS');

Tried to convert but that's giving an error.

timestamp := TO_CHAR(CURRENT_TIMESTAMP (TO_NUMBER(settime)/86400),'MM/DD/YYYY HH24:MI:SS');

CodePudding user response：

From here Data formatting

to_number ( text, text ) → numeric

Converts string to numeric according to the given format. to_number('12,454.8-', '99G999D9S') → -12454.8.

You need to provide a number format. See the rest of the link for what those are.

If settime is actually all number characters you can skip the formatting and do:

select '1234'::integer, '1.25'::numeric;
 int4 | numeric 
------ ---------
 1234 |    1.25

So something like:

settime::integer/86400

CodePudding user response：

That should be simple with date arithmetic:

to_char(clock_timestamp()   settime * INTERVAL '1 second', 'MM/DD/YYYY HH24:MI:SS')

CodePudding user response：

If settime is supposed to be milliseconds, then just add them as milliseconds. Postgres offers the handy function make_interval() that makes this easy:

current_timestamp   make_interval(secs => settime::double precision / 1000)

It's unclear to me what values settime contains. The above assumes those are seconds.

The result is a proper timestamp value that you can format using to_char(). However if you are storing that in a variable (which the assignment timestamp := ... suggests) then I would strongly recommend to not handle the timestamp as a string but declare the variable timestamp with the data type timestamp. Handling everything as strings is a really bad way of doing things (in Postgres just as well as in Oracle). A migration might be a good opportunity to cleanup that.