Progress/Open Edge database. Getting date and time from a field that is all seconds.

Please try command below - remove limit 10 to get all rows:

select TO_CHAR(TO_TIMESTAMP((start-time) / 1000), 'DD/MM/YYYY HH24:MI:SS'),start-time from PUB.sfeventcds limit 10;

Select allOpen in new window

Hello,,  
I Tried the statement

select TO_CHAR(TO_TIMESTAMP((start-time) / 1000), 'DD/MM/YYYY HH24:MI:SS'),start-time from PUB.sfeventcds


Error during Prepare
 S1000(-210056)[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "time) / 1000), 'DD/MM/YYYY HH24:MI:SS')," (10713) (0.11 secs)

Hi All,  been a way for a while, but i need to revisit this on how to get the time value out of the integer value stored in a Progress DB.  
SELECT   to_date('19900101','yyyymmdd') + "start-time"/86400 as startDate,       -----This lines returns the date from the start time.
         "start-time",
         to_timestamp('19900101','yyyymmdd') + "start-time"/86400 as startDate,
         TO_CHAR(TO_TIMESTAMP((start-time) / 1000), 'DD/MM/YYYY HH24:MI:SS')    ---this gives and error at execution,
FROM     PUB."sfeventcds"


or during Prepare
 S1000(-210056)[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "time) / 1000), 'DD/MM/YYYY HH24:MI:SS')," (10713) (0.11 secs)

There has to be away to get that value,  I was able to use just the date, but now I need the time to process the data correctly.

Any help or Ideas or any doc would be appreciated.   I am not all tht familiar with Porgress, so any help would sure be appreciated.
Thanks

This is really just a guess (it's been years since I used Progress), but try putting that last reference to start-time in double quotes, like all of the other references.  So, that line will be:

TO_CHAR(TO_TIMESTAMP(("start-time") / 1000), 'DD/MM/YYYY HH24:MI:SS')

 It also seems like you'd want to (or have to) give that column a name, so you may want to add something like "as startTime" (without the quotes) at the end:

TO_CHAR(TO_TIMESTAMP(("start-time") / 1000), 'DD/MM/YYYY HH24:MI:SS') as startTime

 James

I tried that and quite a few other things I have found this doc https://www.postgresql.org/docs/9.1/functions-formatting.html
but cannot get the correct format---how to get the 12:36:44 out of the 917872604

Field Value=  :   start-time =   917872604  
start date returns the correct date   2019-02-01
but I need to get the time  from that field..     it should equal 12:36:44    when I run it thru a converter program on the web.  

SELECT   to_date('19900101','yyyymmdd') + "start-time"/86400 as startDate,
         "start-time"
FROM     PUB."sfeventcds"

Thanks
Terry

Adding quotes didn't get rid of the error?  It looked like it didn't understand start-time, so I thought the quotes would fix that.  IAC ...

 What happens if you try just TO_TIMESTAMP(917872604)  ?  I assume that it returns a date and time, but not the correct ones.

 I don't have a lot of time right now, but I'm thinking that maybe you just need to start with something like:

TO_TIMESTAMP("start-time" + 7305)

 According to that page in your link, to_timestamp will convert a Unix time (seconds since 01/01/1970) to a timestamp.  It seems that your start-time is the seconds since 01/01/1990, so, in theory, adding the days in between (7305) will give you the correct date and time.

 James

Error during Prepare
 S1000(-20008)[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Inconsistent types (7481) (0.30 secs)
TO_TIMESTAMP("start-time" + 7305)

-----
SELECT   "start-time",
         to_date('19900101','yyyymmdd') + "start-time"/86400 as startDate1,
         to_timestamp('19900101','yyyymmdd') + "start-time"/86400 + 7305 as startDate2
        -- to_date('19900101','yyyymmdd') + "start-time" + 7305 as startDate3
FROM     PUB."sfeventcds"
WHERE    "kjobcode" = 500134
AND      "mach-id" = '41-1001'
ORDER BY "kjobcode", "seq-code"


'938963339','2019-10-03','1990-01-01 00:00:18.172',
'938963339','2019-10-03','1990-01-01 00:00:18.172',
'938964702','2019-10-03','1990-01-01 00:00:18.172',
'938965167','2019-10-03','1990-01-01 00:00:18.172',
'938965167','2019-10-03','1990-01-01 00:00:18.172'

Did you try to_timestamp(917872604) ?

 Does to_timestamp("start-time") or to_timestamp(start-time) work, or do they both give you an error?

 If both of those give you an error, what data type is start-time?

 to_date('19900101','yyyymmdd') + "start-time"/86400 + 7305 (your startDate3 line, but with /86400 added to it) is kind of an interesting idea.  It's probably not that simple, but it might be worth a try.

 Edit:
 I just did some playing around on SQL Fiddle, and this seemed to work:

to_timestamp("start-time" + (7305 * 86400))

 That converted your start-time to a timestamp, with the date and time (and the dates appeared to be correct, eg. 10/03 for 938963339).

 Or, if you just want the time, without the date, you can use:

to_char(to_timestamp("start-time"), 'HH12:MI:SS AM')

 Without the '+ (7305 * 86400)', the date will be wrong, but if you're only looking at the time, that doesn't matter.

 If those expressions give you errors, then, like I asked above, what data type is start-time?

 Here's a link to the SQL Fiddle page with my tests:

http://sqlfiddle.com/#!15/3b57b/2/0

 James

Have not had a chance to look at this for a solution.

The start time is an integer,
Error during Prepare
 S1000(-20008)[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Inconsistent types (7481) (0.23 secs)

Which expression gives you that error?

 James

Sorry--

SELECT   "start-time",
         to_date('19900101','yyyymmdd') + "start-time"/86400 as startDate1,
         to_timestamp('19900101','yyyymmdd') + "start-time"/86400 + 7305 as startDate2,
        to_timestamp("start-time" + (7305 * 86400))                                -------this statement
       -- to_char(to_timestamp("start-time"), 'HH12:MI:SS AM')              -------this statement also.      
FROM     PUB."sfeventcds"
WHERE    "kjobcode" = 500134
AND      "mach-id" = '41-1001'
ORDER BY "kjobcode", "seq-code"