how to create in mysql a date and time column with a correct format to display in the browser?

Question

I am trying to create a table in MySql with a date and time column in which I put the TIMESTAMP data type with a default value of CURRENT_TIMESTAMP but it returns an incorrect format like this:

2022-06-12T00:36:20.000Z

(I HAVE ALSO TRIED TO USE THE DATETIME DATA TYPE, IT RETURNS THE SAME THING)

instead of returning the date and time in the correct format as shown in a select CURRENT_TIMESTAMP of the terminal, it returns something like this and with 3 HOURS IN ADVANCE, how could I solve this?

CREATE TABLE mensajes (
  id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(100) NOT NULL,
  msg TEXT NOT NULL,
 `date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO mensajes (email,msg) VALUES('you@gmail.com','Hello World');

[socket.js]

let socket = io();

const msg = document.querySelector("#msg");
const btnChat = document.querySelector("#chatBtn");
const chat = document.querySelector("#chat");
const email = document.querySelector("#email");
const form = document.querySelector("#form");
const formx = document.querySelector("#formx");

form.addEventListener("submit", (e) => {
  e.preventDefault();
  if (email.value === "" || msg.value === "") {
    alert("Email and message are required");
  } else {
    socket.emit("chat", {
      email: email.value,
      msg: msg.value,
    });
    msg.value = "";
  }
});

socket.on("new-products", () => {
  fetch("api/productos")
    .then((response) => {
      return response.json();
    })
    .then(function (data) {
      if (data.length < 1) {
        return (document.querySelector(
          "tbody"
        ).innerHTML = `<h2 class='text-center bg-warning'>Start adding some products to view the collection...</h2>`);
      } else {
        let tbody = document.querySelector("tbody");
        let thead = document.querySelector("thead");
        let tr = document.createElement("tr");
        let th = document.createElement("th");
        th.innerHTML = "#";
        th.setAttribute("scope", "col");
        tr.appendChild(th);
        th = document.createElement("th");
        th.innerHTML = "Title";
        th.setAttribute("scope", "col");
        tr.appendChild(th);
        th = document.createElement("th");
        th.innerHTML = "Price";
        th.setAttribute("scope", "col");
        tr.appendChild(th);
        th = document.createElement("th");
        th.innerHTML = "     Preview";
        th.setAttribute("scope", "col");
        tr.classList.add("text-center");
        tr.appendChild(th);
        thead.appendChild(tr);
        data.forEach((product) => {
          let tr = document.createElement("tr");
          tr.classList.add("text-center");
          let th = document.createElement("th");
          th.setAttribute("scope", "row");
          th.innerHTML = product.id;
          let td2 = document.createElement("th");
          td2.setAttribute("scope", "row");
          let td3 = document.createElement("th");
          td3.setAttribute("scope", "row");
          let td4 = document.createElement("th");
          td4.setAttribute("scope", "row");
          let img = document.createElement("img");
          img.src = product.img;
          img.style.width = "80px";
          img.style.height = "80px";
          td2.innerText = product.title;
          td3.innerText = `$${product.price}`;
          td4.appendChild(img);
          tr.appendChild(th);
          tr.appendChild(td2);
          tr.appendChild(td3);
          tr.appendChild(td4);
          tbody.appendChild(tr);
        });
      }
    });
});

socket.on("chat", () => {
  fetch("api/chat")
    .then((response) => {
      return response.json();
    })
    .then((data) => {
      chat.innerHTML = "";
      data.forEach((dato) => {
        return (chat.innerHTML += `<div class="card">
        <div class="card-header bg-light">
          <h5 class="card-title text-primary">${dato.email}</h5>
          <h6 class="card-subtitle bg-light text-success mb-2  ">${dato.fecha}</h6>
        </div>
        <div class="card-body bg-dark">
          <p class="card-text text-warning bg-dark">${dato.msg}</p>
        </div>
      </div>
      <br>`);
      });
    });
});

[chat.ejs]

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Document</title>
    <style>
      .divchat {
        width: 100%;
        height: 56.7vh;
 
        overflow: auto;
        margin-top: 7%;
       
      }
    </style>
  </head>
  <body class="container" style="min-height: 100vh;">
    <div class=" bg-light divchat">
      <h2 class="text-dark  bg-light ms-2 fs-2">MESSAGE CENTER</h2>
      <div class="text-center bg-light">
        <form action="/" method="POST" id="form">
          <input
            style="outline: none; border: none"
            class="form-control shadow-none fs-4 text-dark bg-light mb-3 w-100"
            type="email"
            maxlength="150"
            required
            name="email"
            placeholder="Enter your email"
            id="email"
          />

          <input
            style="outline: none; border: none"
            class="form-control shadow-none fs-4 text-dark bg-light mb-3 w-100"
            type="text"
            maxlength="314"
            name="msg"
            placeholder="Type you message here..."
            id="msg"
            required
          />
          <button
            style="font-weight: bold"
            type="submit"
            class="btn w-25 fs-3 btn-success"
            name="chatBtn"
            id="chatBtn"
          >
            SEND
          </button>
        </form>
        <br />
      </div>
      <div>
        <ul class="text-dark" id="chat" style="font-weight: bold; margin-top:3%;"></ul>
      </div>
    </div>
    <script
      src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/js/bootstrap.bundle.min.js"
      integrity="sha384-pprn3073KE6tl6bjs2QrFaJGz5/SUsLqktiwsUTF55Jfv3qYSDhgCecCxMW52nD2"
      crossorigin="anonymous"
    ></script>
    <script src="/socket.io/socket.io.js"></script>
  </body>
</html>

MODEL[chat.js]

const { knex } = require("../db/db");

class Mensajes {
  constructor(table) {
    this.table = table;
    this.knex = knex;
  }

  async getMessages() {
    try {
      const chat = await this.knex(this.table);
      return chat;
    } catch (error) {
      console.log(error);
    }
  }
  async createMessages(data) {
    try {
      const chat = await this.knex(this.table).insert(data);
      return chat;
    } catch (error) {
      console.log(error);
    }
  }

  async deleteAllMessages() {
    try {
      const chat = await this.knex(this.table).del();
      return chat;
    } catch {
      console.log(error);
    }
  }
}

module.exports = Mensajes;

[chatRouter.js]

const express = require("express");
const Mensajes = require("../models/chat");
const router = express.Router();
const chatService = new Mensajes("Mensajes");

router.get("/", async (req, res) => {
  res.status(200).json(await chatService.getMessages());
});

router.post("/", async (req, res) => {
  const mensaje = chatService.createMessages(req.body);

  if (mensaje) {
    return res
      .status(201)
      .json({ message: "Mensaje creado", mensaje: await mensaje });
  } else {
    res.status(400).json({ message: "Error creating the message" });
  }
});

router.delete("/", async (req, res) => {
  const mensaje = chatService.deleteAllMessages();
});

module.exports = router;

How is this related to JavaScript? It looks purely like an SQL question to me. — Jack Bashford, Jun 12 '22 at 00:52
Formatting the date is not mysql's job. Once you get the date to the browser, then in javascript you format the date. You may find this answer useful https://stackoverflow.com/questions/2613511/javascript-dateformat-for-different-timezones — Rocky Sims, Jun 12 '22 at 01:00
The `3 HOURS IN ADVANCE` issue is likely that your server is running GMT, not the timezone you are in. You can change that but it's probably easier to just adjust on output. https://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql — user3783243, Jun 12 '22 at 02:17
Does this answer your question? [Can MySQL convert a stored UTC time to local timezone?](https://stackoverflow.com/questions/2187593/can-mysql-convert-a-stored-utc-time-to-local-timezone) — kmoser, Jun 12 '22 at 21:02

score 0 · Answer 1 · answered Jun 12 '22 at 01:12

Here is a way to go from the iso date time string (received from the database) to a localized, formatted date time string.

const isoDateStr = '2022-06-12T00:36:20.000Z';
const date = new Date(isoDateStr);

const dateStr = date.toLocaleDateString('en-US', {timeZone: 'America/Denver'});
const timeStr = date.toLocaleTimeString('en-US', {timeZone: 'America/Denver'});

console.log(`${dateStr} ${timeStr}`);

score 0 · Answer 2 · answered Jun 12 '22 at 02:32

If it's only to display on the web, you could use the Php function strtotime.

For example:

<?php
$time = "2022-06-12T00:36:20.000Z";
echo date('H:i', strtotime($time))." → 1st format<br />";
echo date('H:i:s', strtotime($time))." → 2nd format<br />";
echo date('F', strtotime($time))." → 3rd format<br />";
echo date('Y-m-d', strtotime($time))." → 4th format<br />";
echo date('jS F, Y', strtotime($time))." → 5th format<br />";
echo date('l dS \o\f F Y h:i:s A', strtotime($time))." → 6th format<br />";
echo "etc...";
?>

That code returns:

02:36 → 1st format

02:36:20 → 2nd format

June → 3rd format

2022-06-12 → 4th format

12th June, 2022 → 5th format

Sunday 12th of June 2022 02:36:20 AM → 6th format

etc...

You can see more on strtotime Php man page and Date and Time Formats

how to create in mysql a date and time column with a correct format to display in the browser?

2 Answers2