Search code examples
sqlpostgresqldatetimedate-arithmetic

How to convert Hours to Numeric in PostgreSQL

I have a table in postgreSQL that have a hour column, i need to convert that column into a numeric one, like if i have 1h and 30 min i need it to be shown as 1.5

I tried to use convert and cast but it didn't worked.

There is a way to do this?

The actual data type of the column is TIME

The actual result show like this:

501001  "DEMANDA"   2019    "Ciclo 10"  "01:30:00"
501001  "DEMANDA"   2019    "Ciclo 11"  "01:30:00"
501001  "DEMANDA"   2019    "Ciclo 12"  "00:30:00"
501001  "DEMANDA"   2019    "Ciclo 2"   "00:30:00"
501001  "DEMANDA"   2019    "Ciclo 3"   "00:30:00"
501001  "DEMANDA"   2019    "Ciclo 4"   "02:00:00"
501001  "DEMANDA"   2019    "Ciclo 5"   "08:00:00"
501001  "DEMANDA"   2019    "Ciclo 6"   "07:00:00"
501001  "DEMANDA"   2019    "Ciclo 7"   "23:00:00"

and i need to show like this:

501001  "DEMANDA"   2019    "Ciclo 10"  "1.5"
501001  "DEMANDA"   2019    "Ciclo 11"  "1.5"
501001  "DEMANDA"   2019    "Ciclo 12"  "0.5"
501001  "DEMANDA"   2019    "Ciclo 2"   "0.5"
501001  "DEMANDA"   2019    "Ciclo 3"   "0.5"
501001  "DEMANDA"   2019    "Ciclo 4"   "2.0"
501001  "DEMANDA"   2019    "Ciclo 5"   "8.0"
501001  "DEMANDA"   2019    "Ciclo 6"   "7.0"
501001  "DEMANDA"   2019    "Ciclo 7"   "23.0"
Solution

  • You could do:

    extract(epoch from mycol) / 60 / 60

    This converts the time to a number of seconds, and then to the corresponding number of hours.