Convert TIMESTAMP field to time

Question

Convert TIMESTAMP field to time

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

I have a field TIMESTAMP in a field of a table in MySQL.

TimeStamp

7/31/2018 5:58
7/31/2018 5:58
7/31/2018 5:57
7/31/2018 5:57
7/31/2018 5:55

I know how to format the field to be able to separate dates and time in Hours, Minutes and Seconds. But I need to convert those three fields to hours.

The operation is like the following:

Hora + (Minutos/60) + (Segundos/3600) = Horas

How can I take this to MySQL? Either with the three fields formatted previously or directly from the field TIMESTAMP .

mysql

asked by Jose Daniel Solis 13.09.2018 в 05:21

source

2 answers

error: invalid use of void expression logic problem when instantiating Javascript class

score 2 · Answer 1

If what you need is to calculate the number of hours (in floating point) from '00: 00: 00 'of the day, then the function you are looking for is:

SELECT
  *,
  TIME_TO_SEC(TIME(fecha)) / 3600 horas
FROM tabla

To calculate the seconds elapsed since '00: 00: 00 'use TIME_TO_SEC() and to obtain only the time part (hour, minutes and seconds) use TIME() .

You can see a example of online operation here :

fecha                  horas
 2018-07-31 05:58:00    5.9667
 2018-07-31 05:58:00    5.9667
 2018-07-31 05:57:00    5.95
 2018-07-31 05:57:00    5.95
 2018-07-31 05:55:00    5.9167

score 1 · Answer 2

You can use the DATE_FORMAT () function to convert the TimeStamp to the format you want. Then you can apply the operations you want. If we consider that the table in which you have the data stored is called "table" and the column in which you save your TimeStamp is called "TimeStampValue", this would leave the query:

SELECT 
       DATE_FORMAT(TimeStampValue, "%h") + DATE_FORMAT(TimeStampValue, "%i")*60 + DATE_FORMAT(TimeStampValue, "%s")*3600 AS HorasSum
      ,DATE_FORMAT(TimeStampValue, "%d/%m/%Y") AS fecha
FROM tabla