upd: use UTC_TIMESTAMP and new format_timestamp

3 files changed, 25 insertions, 5 deletions

diff --git a/database.sql b/database.sql
index cff3e93..4840f22 100644
--- a/database.sql
+++ b/database.sql
@@ -3,7 +3,7 @@ CREATE TABLE IF NOT EXISTS users (
     username TEXT NOT NULL UNIQUE,
     password TEXT,
     secret_key TEXT NOT NULL,
-    joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+    joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
 );
 
 CREATE TABLE IF NOT EXISTS connections (
@@ -12,7 +12,7 @@ CREATE TABLE IF NOT EXISTS connections (
     alias_id TEXT NOT NULL,
     platform TEXT NOT NULL,
     data TEXT NOT NULL,
-    connected_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+    connected_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
 );
 
 CREATE TABLE IF NOT EXISTS emotes (
@@ -21,7 +21,7 @@ CREATE TABLE IF NOT EXISTS emotes (
     mime TEXT NOT NULL,
     ext TEXT NOT NULL,
     uploaded_by INTEGER REFERENCES users(id),
-    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+    created_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
 );
 
 CREATE TABLE IF NOT EXISTS emote_sets (
@@ -38,7 +38,7 @@ CREATE TABLE IF NOT EXISTS emote_set_contents (
     emote_id INTEGER NOT NULL REFERENCES emotes(id),
     name TEXT,
     added_by INTEGER NOT NULL REFERENCES users(id),
-    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+    added_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
 );
 
 CREATE TABLE IF NOT EXISTS acquired_emote_sets (
diff --git a/src/emotes/single_page.php b/src/emotes/single_page.php
index d338565..92b4e24 100644
--- a/src/emotes/single_page.php
+++ b/src/emotes/single_page.php
@@ -118,7 +118,9 @@ include_once "../../src/config.php";
                                 echo $username;
                                 echo "</a>";
 
-                                echo ", " . date("d M Y", $emote->get_created_at());
+                                echo ', <span title="';
+                                echo date("M d, Y H:i:s", $emote->get_created_at());
+                                echo ' UTC">about ' . format_timestamp(time() - $emote->get_created_at()) . " ago</span>";
                                 ?></td>
                             </tr>
                             <tr>
diff --git a/src/utils.php b/src/utils.php
index 59ecad4..4954580 100644
--- a/src/utils.php
+++ b/src/utils.php
@@ -30,4 +30,22 @@ function str_safe(string $s, int|null $max_length, bool $remove_new_lines = true
     $output = trim($output);
 
     return $output;
+}
+
+function format_timestamp(int $timestamp_secs)
+{
+    $days = floor($timestamp_secs / (60 * 60 * 24));
+    $hours = floor($timestamp_secs / (60 * 60) % 24);
+    $minutes = floor($timestamp_secs % (60 * 60) / 60);
+    $seconds = floor($timestamp_secs % 60);
+
+    if ($days == 0 && $hours == 0 && $minutes == 0) {
+        return "$seconds second" . ($seconds > 1 ? "s" : "");
+    } else if ($days == 0 && $hours == 0) {
+        return "$minutes minute" . ($minutes > 1 ? "s" : "");
+    } else if ($days == 0) {
+        return "$hours hour" . ($hours > 1 ? "s" : "");
+    } else {
+        return "$days day" . ($days > 1 ? "s" : "");
+    }
 }
\ No newline at end of file