We help IT Professionals succeed at work.

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

tambrosi
tambrosi used Ask the Experts™
on
I am new to Progress/Open Edge database.  I need to get the date and time from field that is defined as seconds.  

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"

Open in new window


Any help would be appreciated.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
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;

Open in new window

Author

Commented:
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)

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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

Author

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

Author

Commented:
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

Author

Commented:
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"
"Inconsistent types" (assuming that that is referring to data types) seems like an odd error to get on that second statement, since you're only using one type at a time (start-time as an argument to to_timestamp, and then the result of to_timestamp as an argument to to_char).

 Just to make sure, try just to_timestamp("start-time") and see if that gives you an error.

 Also, you could try giving a name to those columns (eg. add 'as startTime1' (without the quotes) to the end of the first expression).  I don't think that that's your problem (I didn't give the columns names in my test code), but no harm in making sure.

 If you look at the SQL Fiddle page in the link that I posted earlier, I created an integer column named start-time and plugged in a couple of the values that you potsed, and both of those expressions worked fine with that column.  FWIW, the documentation in the link that you posted earlier was for 9.1, and the oldest version on SQL Fiddle was 9.3, so, if you're using 9.1, it's not quite the same version.  But this seems like pretty basic stuff, so I kind of doubt that the problem is something that changed between 9.1 and 9.3 (although I could be wrong).

 James