3.4.10: Dates in SQL and JS

Search…
Introduction
Postgres has several data types to represent time data. The following are some best practices for dealing with this data. Read more on Postgres date and time data types here.
SQL Tables Should Have created_at
Most of our tables should have a column called created_at. This is a timestamp that shows when each row was created. This is easy to set as a column in our CREATE TABLE statements.
CREATE TABLE recipes (
    id            SERIAL PRIMARY KEY,
    label         TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
When we create a new row in this table, Postgres will set a timestamp of when we inserted the row.
INSERT INTO recipes (label) VALUES ('Udon');
When we retrieve the record we see the created_at value is set automatically.
SELECT * FROM recipes;
TIMESTAMPTZ as Default Datetime Data Type
The Postgres date data type with the most information in it is TIMESTAMPTZ. As a best practice we should set the database column to this specific type. The value put in the database is set to the date and timezone at the computer that it's running on.
If your app never deals with saving and displaying data in different timezones then you may never need this data, but it doesn't cause any problems to record it. If the scope of your app expands later, it would be impossible to capture or properly deal with this data.
The author of the pg library suggests using TIMESTAMPTZ here. More information on JavaScript applications needing timezone information here.​
Forms to Edit TIMESTAMPTZ Data
For created_at data, which records the date, time and timezone of a row, we don't have any easy way to render that date data into an edit form (although in real life you would probably never want to edit this information).
If we did want to render an edit form for this data we would need to render a separate form for each piece, i.e, one input for data, another for time, another for timezone. The fundamental problem is that there is no such HTML form which can properly set date and time together.
We'll get around this limitation of HTML forms later when we can do DOM manipulation again in the next module. Note that there exists a datetime-local input type in HTML but that it is not cross-browser compatible.​
Avoid TIMESTAMPTZ for User-Generated Data (For Now)
Given the above limitation on rendering and capturing date and time data in pure HTML forms, we can minimise headache by using the DATE data type in our tables for user-generated date data.
CREATE TABLE sightings (
    id            SERIAL PRIMARY KEY,
    description   TEXT,
    date          DATE,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
Note in the sightings example above we make the distinction between created_at, the date the row was created in the database, and date, the date of the UFO sighting. created_at is of type TIMESTAMPTZ because it is auto-generated and does not require a form input.
Capturing Date Data in Forms
For dates (not including time) we can use the date input type in a form. In production applications this would also not be ideal, since date input types are also not well supported (about 90%), but they are much more prevalent than the datetime input type. See browser compatibility of the date input type here.
<input type="date" name="date"/>
Because the DATE type in the Postgres table we specified above matches the input format we specified in the input form, we can simply pass what's in request.body.date right into the database.
app.post("/sightings", (request, response) => {
  const sightingValues = [request.body.description, request.body.date];
​
  const sightingInsertQuery =
    "INSERT INTO sightings (description, date) VALUES ($1, $2) RETURNING *";
​
  pool.query(sightingInsertQuery, sightingValues, (error, result) => {
    if (error) {
      console.log(error);
      response.status(501).send("error!");
      return;
    }
​
    console.log(result.rows);
    response.send("success");
  });
});
Date Output Format
The pg npm library processes the data coming out of the database and transforms it into native JavaScript types. When we get the value of a DATE SQL type out of the database it's a JavaScript Date Object. We can call any of the methods on this value that are specified in the JS Date documentation.
app.get("/sightings", (request, response) => {
  const query = "SELECT * from sightings";
​
  pool.query(query).then((result) => {
    // print out all the dates in the database
    const allSightingDates = result.rows.map((sighting) => {
      console.log(sighting.date);
​
      // change this Date object into a string value
      return sighting.date.toString();
    });
​
    response.send(allSightingDates);
  });
});
Advanced Date Formatting
If we want to format the date in a fancier way we can use the moment library.
npm install moment
import moment from "moment";
Format date data by the amount of time from now.
// ...
​
// print out all the dates in the database
const allSightingDates = result.rows.map((sighting) => {
  console.log(sighting.date);
​
  // change this Date object into a string value
  // .from returns a "ago" string
  return moment(sighting.date).from();
});
​
// ...
The output would be something like this.
["11 days ago", "117 years ago"];
See the moment docs on how to output dates with moment. Note that moment will also work with the TIMESTAMPTZ data type from the created_at column and any date with timezone data inside it.
Current Options for Storing Time Data
If your app requires time data, based on the technologies we have learned so far, we have a few options. We'll look at more advanced date time input methods with browser-side JavaScript in the next module.
1.Disregard cross-browser compatibility and use the datetime-local input type. Chrome supports datetime-local and the field value can be inserted directly into a Postgres TIMESTAMPTZ field.
2.Use the date input type and create a separate input field for time data. Store time data in Postgres using a separate column. Example here. In production applications we would always want to store timezone data, but in Coding Bootcamp Module 3 it's not recommended due to input limitations.
3.Avoid time input altogether in your application, and only support date input.
3.4.9: Database Setup Files
3.4.11: File Uploads
Last modified 3mo ago
Copy link
Outline
Introduction
SQL Tables Should Have created_at
TIMESTAMPTZ as Default Datetime Data Type
Forms to Edit TIMESTAMPTZ Data
Avoid TIMESTAMPTZ for User-Generated Data (For Now)
Capturing Date Data in Forms
Date Output Format
Advanced Date Formatting
Current Options for Storing Time Data