如何在MySQL的API调用中格式化日期时间格式？
前端代码：
import React, { Component, useState, useEffect } from "react";
import Navbar from "../Navbar/Navbar.js";
import BarChart from "../BarChart/BarChart";
import {
Chart,
Tooltip,
CategoryScale,
LinearScale,
Title,
LineController,
LineElement,
PointElement,
} from "chart.js";
import Card from "../Card/Card.js";
import CardHeader from "../Card/CardHeader.js";
import CardIcon from "../Card/CardIcon.js";
import CardBody from "../Card/CardBody";
import CardFooter from "../Card/CardFooter";
import GridItem from "../Grid/GridItem.js";
import GridContainer from "../Grid/GridContainer.js";
import { makeStyles } from "@material-ui/core/styles";
import Table from "../Table/Table";
import Icon from "@material-ui/core/Icon";
import Danger from "../Typography/Danger.js";
import Warning from "@material-ui/icons/Warning";
import DateRange from "@material-ui/icons/DateRange";
import Update from "@material-ui/icons/Update";
import Store from "@material-ui/icons/Store";
import LocalOffer from "@material-ui/icons/LocalOffer";
import Accessibility from "@material-ui/icons/Accessibility";
// import { Icon, InlineIcon } from '@iconify/react';
import contentCopy from "@iconify-icons/mdi/content-copy";
import styles from "../../assets/jss/material-dashboard/views/dashboardStyle.js";
import Map from "../Map/map";
import Axios from "axios"; //axios library to make requests to api
import "./Home.css";
import ReactPaginate from "react-paginate";
import ReactExport from "react-data-export";
const ExcelFile = ReactExport.ExcelFile;
const ExcelSheet = ReactExport.ExcelFile.ExcelSheet;
Chart.register(
Tooltip,
CategoryScale,
LinearScale,
Title,
LineController,
LineElement,
PointElement
);
function Home(props) {
//make an axios request to get intents data from database
const [intentsList, setintentsList] = useState([]);
useEffect(() => {
Axios.get("http://localhost:3001/intents").then((response) => {
setintentsList(response.data);
});
}, []);
const [customerList, setCustomerList] = useState([]); //store all that information of the database in a list
//make an axios request to get information from database
useEffect(() => {
Axios.get("http://localhost:3001/customers").then((response) => {
setCustomerList(response.data);
});
}, []);
const updateCustomerContacted = (ID) => {
Axios.put("http://localhost:3001/update", {
contacted: newContacted,
ID: ID,
}).then((response) => {
setCustomerList(
customerList.map((val) => {
const dateStr = new Date(val.latest_time_of_visit).toLocaleDateString(
"en-CA"
);
const timeStr = new Date(
val.latest_time_of_visit
).toLocaleTimeString();
const dateTime = `${dateStr} ${timeStr}`;
return val.ID == ID
? {
ID: val.ID,
name: val.name,
email: val.email,
counts_of_visit: val.counts_of_visit,
latest_time_of_visit: dateTime,
contacted: newContacted,
}
: val;
})
);
});
};
//delete function
const deleteCustomer = (ID) => {
Axios.delete(`http://localhost:3001/stats/delete/${ID}`).then(
(response) => {
setCustomerList(
customerList.filter((val) => {
return val.ID != ID;
})
);
}
);
};
//pagination
const [pageNumber, setPageNumber] = useState(0);
const customersPerPage = 5; //change this number according to desired number of rows in a page
const pagesVisited = pageNumber * customersPerPage;
const displayCustomers = customerList
.slice(pagesVisited, pagesVisited + customersPerPage)
.map((val, key) => {
const dateStr = new Date(val.latest_time_of_visit).toLocaleDateString(
"en-CA"
);
const timeStr = new Date(val.latest_time_of_visit).toLocaleTimeString();
const dateTime = `${dateStr} ${timeStr}`;
return (
<tr>
<td>{val.name}</td>
<td>{val.email}</td>
<td>{val.counts_of_visit}</td>
<td>{dateTime}</td>
<td>{val.contacted}</td>
<td>
<select
onChange={(event) => {
setNewContacted(event.target.value);
}}
>
<option value="" selected disabled hidden>
Select Yes/No
</option>
<option value="Yes">Yes</option>
<option value="No">No</option>
</select>
<button
className="btn btn-primary"
onClick={() => {
updateCustomerContacted(val.ID);
}}
>
Update
</button>
</td>
<td>
<button
className="btn btn-danger"
onClick={() => {
deleteCustomer(val.ID);
}}
>
Delete
</button>
</td>
</tr>
);
});
//to account for the fact that total number of customers cannot be divided equally among the pages
const pageCount = Math.ceil(customerList.length / customersPerPage);
//page change
const changePage = ({ selected }) => {
setPageNumber(selected);
};
//update contacted column
const [newContacted, setNewContacted] = useState(0);
{
/*}
const [currentTime, setCurrentTime] = useState(1);
useEffect(() => {
fetch("/time")
.then((res) => res.json())
.then((data) => {
setCurrentTime(data.time);
});
}, []);
*/
}
//export to csv function
const DataSet = [
{
columns: [
{
title: "S/N",
style: { font: { sz: "18", bold: true } },
width: { wpx: 100 },
}, // width in pixels
{
title: "Customer Information",
style: { font: { sz: "18", bold: true } },
width: { wpx: 250 },
}, // width in pixels
{
title: "Customer Email",
style: { font: { sz: "18", bold: true } },
width: { wpx: 250 },
}, // width in pixels
{
title: "Counts of Visit",
style: { font: { sz: "18", bold: true } },
width: { wpx: 175 },
}, // width in pixels
{
title: "Latest Time of Visit",
style: { font: { sz: "18", bold: true } },
width: { wpx: 250 },
}, // width in pixels
{
title: "Contacted?",
style: { font: { sz: "18", bold: true } },
width: { wpx: 250 },
}, // width in pixels
],
data: customerList.map((val) => [
{ value: val.ID, style: { font: { sz: "14" } } },
{ value: val.name, style: { font: { sz: "14" } } },
{ value: val.email, style: { font: { sz: "14" } } },
{ value: val.counts_of_visit, style: { font: { sz: "14" } } },
{ value: val.latest_time_of_visit, style: { font: { sz: "14" } } },
{ value: val.contacted, style: { font: { sz: "14" } } },
]),
},
];
const useStyles = makeStyles(styles);
const classes = useStyles;
return (
<div>
<Navbar />
<GridContainer>
<GridItem xs={12} sm={6} md={6}>
{" "}
{/*width for different screen sizes*/}
<Card>
<CardHeader color="warning" stats icon>
<CardIcon color="danger">
<h4 className={classes.cardTitleWhite}>Product Statistics</h4>
{/* <Icon>content_copy</Icon> */}
</CardIcon>
<p className={classes.cardCategory}>Used Space</p>
<h3 className={classes.cardTitle}>
49/50 <small>GB</small>
</h3>
</CardHeader>
<CardBody>
<BarChart />
</CardBody>
<CardFooter stats>
<div className={classes.stats}>
<Danger>
<Warning />
</Danger>
<a href="#pablo" onClick={(e) => e.preventDefault()}></a>
</div>
</CardFooter>
</Card>
</GridItem>
<GridItem xs={12} sm={6} md={6}>
<Card>
<CardHeader color="dark" stats icon>
<CardIcon color="dark">
{/* <Store /> */}
<h4 className={classes.cardTitleWhite}>Locations</h4>
</CardIcon>
<p className={classes.cardCategory}>Revenue</p>
<h3 className={classes.cardTitle}>$34,245</h3>
</CardHeader>
<CardBody>
<Map />
</CardBody>
<CardFooter stats>
<div className={classes.stats}>
<DateRange />
</div>
</CardFooter>
</Card>
</GridItem>
{/* <GridItem xs={12} sm={6} md={6}>
<Card>
<CardHeader color="danger" stats icon>
<CardIcon color="danger">
<Icon>info_outline</Icon>
</CardIcon>
<p className={classes.cardCategory}>Fixed Issues</p>
<h3 className={classes.cardTitle}>75</h3>
</CardHeader>
<CardFooter stats>
<div className={classes.stats}>
<LocalOffer />
</div>
</CardFooter>
</Card>
</GridItem>
<GridItem xs={12} sm={6} md={6}>
<Card>
<CardHeader color="info" stats icon>
<CardIcon color="info">
<Accessibility />
</CardIcon>
<p className={classes.cardCategory}>Followers</p>
<h3 className={classes.cardTitle}>+245</h3>
</CardHeader>
<CardFooter stats>
<div className={classes.stats}>
<Update />
</div>
</CardFooter>
</Card>
</GridItem> */}
</GridContainer>
<GridContainer>
<GridItem xs={12} sm={12} md={12}>
<Card>
<CardHeader color="warning">
<h4 className={classes.cardTitleWhite}>Customer Information</h4>
{/*}
<p className={classes.cardCategoryWhite}>
Visitors since 16th May, 2021
</p>
*/}
</CardHeader>
<CardBody>
<div className="dashboardcontainer">
<div className="container"></div>
<table className="customertable">
<thead>
<tr>
<th>Customer Name</th>
<th>Customer Email</th>
<th>Counts of Visit</th>
<th>Latest Time of Visit</th>
<th>Contacted?</th>
<th>Edit Contacted</th>
<th>Action</th>
</tr>
</thead>
<tbody>{displayCustomers}</tbody>
</table>
<ReactPaginate
previousLabel={"Previous"}
nextLabel={"Next"}
pageCount={pageCount}
onPageChange={changePage}
containerClassName={"paginationBttns"}
pageLinkClassName={"paginationNumber"}
previousLinkClassName={"previousBttn"}
nextLinkClassName={"nextBttn"}
disabledClassName={"paginationDisabled"}
activeClassName={"paginationActive"}
/>
<ExcelFile
filename="Customer Information"
element={
<button
type="button"
className="btn btn-success float-right m-3"
>
Export to CSV
</button>
}
>
<ExcelSheet
dataSet={DataSet}
name="Customer Information Report"
></ExcelSheet>
</ExcelFile>
</div>
{/*}
<Table
tableHeaderColor="warning"
tableHead={["ID", "Name", "Salary", "Country"]}
tableData={[
["1", "Dakota Rice", "$36,738", "Niger"],
["2", "Minerva Hooper", "$23,789", "Curaçao"],
["3", "Sage Rodriguez", "$56,142", "Netherlands"],
["4", "Philip Chaney", "$38,735", "Korea, South"],
]}
/>
*/}
</CardBody>
</Card>
</GridItem>
</GridContainer>
</div>
);
}
export default Home;
后端代码：
//set up express server
const express = require("express");
const app = express();
//set up sql server
const mysql = require("mysql");
const cors = require("cors");
app.use(cors());
app.use(express.json());
//create a variable called db to make your SQL Statements
const db = mysql.createConnection({
user: "",
host: "",
password: "",
database: "",
});
//GET REQUEST to database to retrieve customers information from database
app.get("/customers", (req, res) => {
db.query("SELECT * FROM customer_info", (err, result) => {
if (err) {
console.log(err);
} else {
let row,
res = [];
console.log(result);
for (let i = 0; i < result.length; i++) {
row = result[i];
let tmpd = row.latest_time_of_visit.replace("Z", "").split("T");
let tmpt = tmpd[1].split(":");
// check for meridian
let meridian = "AM";
if (+tmpt[0] > 12) {
tmpt[0] = +tmpt[0] - 12;
meridian = "PM";
tmpt[0] = tmpt[0] < 10 && "0" + tmpt[0];
}
row.latest_time_of_visit =
tmpd[0] + " " + tmpt.join(":") + " " + merdian;
res.push(row);
}
res.send(res);
}
});
});
//Update customers
app.put("/update", (req, res) => {
const ID = req.body.ID;
const contacted = req.body.contacted;
db.query(
"UPDATE customer_info SET contacted = ? WHERE ID = ?",
[contacted, ID],
(err, result) => {
if (err) {
console.log(err);
} else {
res.send(result);
}
}
);
});
//Delete customers
app.delete("/stats/delete/:ID", (req, res) => {
const ID = req.params.ID;
db.query("DELETE FROM customer_info WHERE ID = ?", ID, (err, result) => {
if (err) {
console.log(err);
} else {
res.send(result);
}
});
});
//api call to pull intents data
app.get("/intents", (req, res) => {
db.query("SELECT * FROM data_analytics", (err, result) => {
if (err) {
console.log(err);
} else {
res.send(result);
}
});
});
//check if backend server is running
app.listen(3001, () => {
console.log("Your server is running on port 3001");
});
而不是： {&Q；ID&Q；：1，名称&Q；：阿里&Q；，电子邮件&Q；：&Q；阿里@gmail.com&Q；，&Q；COUNTS_of_ACCESS&Q；：5，"；latest_time_of_visit"；："；2021-05-10T00:01:02.000Z"；，&Q；联系&Q；：&Q；是&Q；}，
当前对MySQL数据库DO API进行了更改。
解决方案
我建议在前端中将日期格式设置为正确的格式。这样，您就可以将日期信息保存在后端，如果您决定在将来更改它，只需简单地调整一下，就可以轻松地更改格式。要做到这一点，一个很好的方法是使用一个库，它有很多库。这里有一个您可以尝试的：date-fns。
相关文章