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('[email protected]','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 >
<div >
<h5 >${dato.email}</h5>
<h6 >${dato.fecha}</h6>
</div>
<div >
<p >${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 style="min-height: 100vh;">
<div >
<h2 >MESSAGE CENTER</h2>
<div >
<form action="/" method="POST" id="form">
<input
style="outline: none; border: none"
type="email"
maxlength="150"
required
name="email"
placeholder="Enter your email"
id="email"
/>
<input
style="outline: none; border: none"
type="text"
maxlength="314"
name="msg"
placeholder="Type you message here..."
id="msg"
required
/>
<button
style="font-weight: bold"
type="submit"
name="chatBtn"
id="chatBtn"
>
SEND
</button>
</form>
<br />
</div>
<div>
<ul id="chat" style="font-weight: bold; margin-top:3%;"></ul>
</div>
</div>
<script
src="https://cdn.jsdelivr.net/npm/[email protected]/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;
CodePudding user response：
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}`);CodePudding user response：
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