I have a table of 20M records in Oracle with a datetime(one of the columns) column of type VARCHAR and in EST timezone. I am trying to create a new column converting the datetime to Unix epoch time but it is throwing me an error.
create function unix_timestamp(pi_date date) return number is
c_base_date constant date := to_date('1970-01-01 00:00:00', 'YYYY-MM-DD HH:MM:SS');
c_seconds_in_day constant number := 24 * 60 * 60;
v_unix_timestamp number;
begin
v_unix_timestamp := trunc((pi_date - c_base_date) * c_seconds_in_day);
if (v_unix_timestamp < 0 ) then
raise_application_error(-20000, 'unix_timestamp:: unix_timestamp cannot be nagative');
end if;
return v_unix_timestamp;
end unix_timestamp;
This is the function I created and when I try to call this function, It is throwing me an error saying:
ORA-01861: literal does not match format string
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.
Can I get some help with it?
Copyright Notice:Content Author:「NAB0815」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/56838075/converting-timestamp-to-unix-epoch-time-in-oracle-sql