0

I want to convert the time_read_epoch value into a readable timeformat, but I can´t get it to work during the table output. The value is saved as a bigint inside the MariaDB database. Maybe someone can help me along here.

$result = $conn->query($sql);
if ($result->num_rows > 0) {
    echo "<table><tr>   <th>Zeitstempel</th>
                        <th>Messwert</th>
                        <th>Postleitzahl</th>
                        <th>Ort</th>
                        <th>Beschreibung Messeinheit</th>
                        <th>Messeinheit</th>
                        </tr>";
    // output data of each row
    while($row = $result->fetch_assoc()) {
      echo  "<tr><td>".$row["time_read_epoch"].
            "</td><td>".$row["value"].
            "</td><td>".$row["plz"].
            "</td><td>".$row["location"].
            "</td><td>".$row["unit_name"].
            " ".$row["unit"]."</td></tr>";
    }
    echo "</table>";
  } else {
    echo "0 results";
  }
  $conn->close()
silx
  • 3
  • 3
  • What you have you tried so far? You want to convert it in SQL or fine to convert at PHP level? – user3783243 May 27 '22 at 17:10
  • If possible at PHP level. I got the conversion from redable format into unix working but backwards as the table gets created row by row it doesnt work so far for me. – silx May 27 '22 at 17:13
  • Does this answer your question? [Convert Epoch Time to Date PHP](https://stackoverflow.com/questions/13477788/convert-epoch-time-to-date-php) – user3783243 May 27 '22 at 17:13
  • Also `FROM_UNIXTIME()` in mysql. – Sammitch May 27 '22 at 17:34

1 Answers1

2

You can do this in your $sql statement (you didn't show it to us) like this.

SELECT 
  FROM_UNIXTIME(tbl_readings.time_read_epoch) AS time_read_epoch,
  whatever...
FROM whatever...

You modify your SQL query to call the FROM_UNIXTIME() function on that BIGINT, then you give the original name of the column as an alias name in the result set.

O. Jones
  • 103,626
  • 17
  • 118
  • 172