topic SQL Select & Epoch time questions... in QlikView

SQL Select & Epoch time questions...

Thu, 20 Feb 2014 16:04:12 GMT

Hi,

I have a database that I am using an ODBC connection to connect to. I can connect to it and pull data. Now i am doing some data modeling and trying to reduce the amount of data and runtime via applying a where clause on a date field. The date field is in epoch time. I would like to set a variable to convert the epoch time to regular time in the script so if I have to use it again, I can simply refer to the variable name. I have the script below, but it returns 'Not a valid month', ORA-01843 . I have tried several variations such as

$(SubmitDate) > '01/01/2013'

$(SubmitDate) > '01/01/2013 12:00:00'

$(SubmitDate) > '01/01/2013 12:00:00 AM'

No dice. Could someone help me out with syntax here?

ODBC

(connection string)

set SubmitDate = (to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + (("SUBMIT_DATE"- 4*3600)/(60*60*24)));

SQL SELECT "ASSIGNED_GROUP",
ASSIGNEE,
"FULL_NAME",
"INCIDENT_NUMBER",
STATUS,
"SUBMIT_DATE",
(to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + (("SUBMIT_DATE"- 4*3600)/(60*60*24))) as SubmitDateTime

FROM ITSMTST."HPD_HELP_DESK_CLASSIC"

where $(SubmitDate) > '1/1/2013';

Re: SQL Select & Epoch time questions...

maxgro — Thu, 20 Feb 2014 17:24:58 GMT

I don't have your table, but this for me works

In submit_date column I have a constant 915148798, just to try your script

Hope it helps you.

set SubmitDate = (to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + ((submit_date - 4*3600)/(60*60*24)));

SQL

select

$(SubmitDate),

to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

from dwh_dl.a

where

$(SubmitDate) > to_date('01/01/1950', 'MM/DD/YYYY')

;

Re: SQL Select & Epoch time questions...

Clever_Anjos — Thu, 20 Feb 2014 17:55:45 GMT

It´s a very commom problem while using ORACLE.

It´s a conversion problem

Oracle expects a date into YYYY-MM-DD format OR you tranform your string using TO_DATE function

I would to

SQL SELECT "ASSIGNED_GROUP",
ASSIGNEE,
"FULL_NAME",
"INCIDENT_NUMBER",
STATUS,
"SUBMIT_DATE",
(to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + (("SUBMIT_DATE"- 4*3600)/(60*60*24))) as SubmitDateTime

FROM ITSMTST."HPD_HELP_DESK_CLASSIC"

where $(SubmitDate) > '2013-01-01';