0

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?

NAB0815
  • 441
  • 4
  • 24
  • 2
    Oracle SQL has functions for this, why are you trying to write your own? Also: why do you have dates in string data type, and not in date data type? And is it EST even in the summer (when pretty much no one uses EST), or is it in fact EDT? –  Jul 01 '19 at 15:10
  • 3
    To your immediate issue: you call the procedure with an input `pi_date` of **date** data type. When you execute it, the input is a string (or so you said). So an implicit conversion takes place. Very likely, your `nls_date_format` doesn't match the date format of those strings. Also, in your code I don't see where you account for the "date-time" being in EST time zone, and not UTC. –  Jul 01 '19 at 15:13
  • Based on the requirement, I had to store it in datetime format in varchar datatype as oracle displays only date not datetime. – NAB0815 Jul 01 '19 at 15:14
  • 3
    "As Oracle displays only date, not datetime"? Where did you learn that? Oracle displays what you tell it to display - set your `nls_date_format` to whatever you need it to show, or use `to_char()` with proper date format model when you select from your data. Besides, the data type is wrong anyway; your times have time zone, and the Oracle data type `date` does not. The correct data type is **timestamp with time zone**. –  Jul 01 '19 at 15:21
  • I see. Thanks for the tip. let me check about that and see if it works. Thanks:) – NAB0815 Jul 01 '19 at 15:22
  • 1
    Storing a date (or timestamp) value in a VARCHAR column is a really, really bad idea to begin with. –  Jul 01 '19 at 15:24
  • Check this one: https://stackoverflow.com/a/54019139/3027266 – Wernfried Domscheit Jul 01 '19 at 17:51

1 Answers1

0

The line of code

c_base_date constant date := to_date('1970-01-01 00:00:00', 'YYYY-MM-DD HH:MM:SS');

has the hour and minute format code wrong; try

c_base_date constant date := to_date('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

MI for minutes (I've forgotten that more times than I can count), and include 24 in the Hour format (HH24). And also look at MathGuy's comment on timezone and comments on date datatype not including timezone information.

Mark Stewart
  • 2,046
  • 4
  • 22
  • 32