Clever Geek Handbook

Convert postgresql spacing format to something usable in php

I have a function in PHP that returns the time elapsed from postgresql database in the following format

00:34:01.024416

in php this data is stored as "String" which is not very useful. What can I use in PHP to do calculations on data provided by the database?

+3


source to share


1 answer


You can convert data from postgres to milliseconds or microseconds. This can be stored in PHP and then used to manage the total time ... A query like the one below will help you with this

SELECT
(EXTRACT (HOURS FROM TIME '00:34:01.024416') * 3600000) :: int AS "hour-millisecond",
(EXTRACT (MINUTE FROM TIME '00:34:01.024416') * 60000) :: int AS "minute-millisecond",
(EXTRACT (SECOND FROM TIME '00:34:01.024416') * 1000) :: int AS "second-millisecond",
(EXTRACT (MILLISECONDS FROM TIME '00:34:01.024416')) :: int AS "millisecond"

      

        
        
        
      

    
SELECT (EXTRACT (HOURS FROM TIME '00:34:01.024416') * 3600000) :: int + 
(EXTRACT (MINUTE FROM TIME '00:34:01.024416') * 60000) :: int + 
(EXTRACT (SECOND FROM TIME '00:34:01.024416') * 1000) :: int + 
(EXTRACT (MILLISECONDS FROM TIME '00:34:01.024416')) :: int AS millisecondelapsed


[EDIT]

SELECT (EXTRACT (HOURS FROM  elapsedtime_result.elapsedtime) * 3600000) :: int + 
(EXTRACT (MINUTE FROM  elapsedtime_result.elapsedtime) * 60000) :: int + 
(EXTRACT (SECOND FROM  elapsedtime_result.elapsedtime) * 1000) :: int + 
(EXTRACT (MILLISECONDS FROM  elapsedtime_result.elapsedtime)) :: int AS millisecondelapsed 
FROM (SELECT current_timestamp - max(createdon) elapsedtime FROM testtable) AS elapsedtime_result
0


source






More articles:


All Articles