Timestamps

• A TIMESTAMP field holds basically both a date and a time (in that order)
• The same properties and formats that apply to each individually apply here as well
• Also have the option of with or without a timezone
  • TIMESTAMP by itself is without a timezone, as per SQL standards
  • TIMESTAMP WITH TIME ZONE is with a timezone. Shorthand in Postgres is TIMESTAMPTZ★
• Internally, timestamps with a timezone are always stored in UTC, but then when displayed are converted back to the local timezone
• Examples:
  • 2022-01-18 14:30:00
  • Jan-18-2022 2:30 PM PST

Intervals

• Represents a span of time
• Generally given by first a number and then by a unit
  • Possible units: microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium
  • Abbreviations or plurals of the above also work
• Examples:
  • 1 day
  • 3 century 2 mins
  • 45 ms
  • 1 mon 87 us
• Intervals can be used in calculations with other date/time data types

MORE!

• These are just a small sample of some of the most common data types
• Postgres supports many more!
  • Booleans
  • Geometric types
  • Network address types
  • JSON types
  • A full list can be found here
• We’ll address others as the come up sporadically through the rest of the semester, but you have the core basics now

Conversions

• There are plenty of instances where you may need to convert between types

  • Sometimes calculations (coming soon!) need a certain data type
  • Sometimes the information was just not stored in the most ideal type

• Can use the CAST function to convert between reasonable data types

• Core syntax is

  CAST(|||column name||| AS |||new data type|||)

• Postgres has a shorthand conversion syntax that uses double colons:

  |||column name|||::|||new data type|||

SQL I/O

Delimited Files

The Classic CSV

id,first_name,last_name,birthday
1,Michael,Scott,"Mar 15, 1964"
2,Dwight,Schrute,"Jan 20, 1970"
3,Pam,Beesly,"Mar 25, 1979"
4,Jim,Halpert,"Oct 1, 1978"
5,Kelly,Kapoor,"Feb 5, 1980"

• Note:
  • The birthday column entries are quoted owing to the comma within them
  • No spaces show up anywhere except within quoted blocks
  • The first line contains a header, which is useful for understanding but contains no actual data
  • Make sure you have no empty line at the end!

Importing Data

• The COPY command will copy information into an existing table, it won’t create the table

  • Thus you are still responsible for creating the table (and associated data types for each column) before COPY will be useful
    • Yes, this can still be painful for huge tables. There are some scripts that can help with it or do some automated checking, but they tend to be far from perfect.

• Syntax of the COPY statement:

  COPY |||tablename|||
  FROM |||full path to file|||
  WITH ( |||import options||| );

Importing Details

Subset Imports

• Sometimes an CSV might not have all the data you want in your SQL table

• You can import all the information from the CSV into only a subset of the SQL table columns

  • As far as I know, you can’t easily go the other direction, importing only a portion of the CSV columns

• Just requires that you specify the target SQL columns after specifying the table

  COPY |||tablename||| (|||column₁|||, |||column₂|||, |||column₃|||)
  FROM |||full path to file|||
  WITH (FORMAT CSV, HEADER);

Understanding Check

CREATE TABLE purchases (
  "date" DATE,
  "store" VARCHAR(20),
  "purchased" TEXT,
  "tot_price" NUMERIC(10,2));

date:store name:purchased
Jan-5-22:Walmart:eggs,milk
Jan-10-22:Roths:cereal,steak
Jan-16-22:Roths:milk,butter
Jan-18-22:Winco:beans

COPY purchases
FROM 'C:\DATA\purchases.csv'
WITH (FORMAT CSV, HEADER);

COPY purchases (date, store, purchased)
FROM 'C:\DATA\purchases.csv'
WITH (FORMAT CSV, HEADER, 
      DELIMITER ':');

COPY purchases (date, store, purchased)
FROM 'C:\DATA\purchases.csv'
WITH (FORMAT CSV, DELIMITER ':');

COPY purchases (date, "store name")
FROM 'purchases.csv'
WITH (FORMAT CSV, HEADER,
      DELIMITER ',');

Exporting Data

• Exporting data takes information from your SQL table and allows you to store it in a text file

  • Note that this process isn’t lossless, as the data types of each column are not stored, just the contents and (maybe) column names

• Syntax-wise, it is almost exactly like copying into a SQL table, except using TO instead of FROM

  COPY |||tablename|||
  TO |||full path to file|||
  WITH ( |||import options||| );

• This exports all columns from the specified table to the filename with the desired format

Exporting Subsets

• In many instances, you may not want all the columns to be exported. In that case, you have a few options:
• Exporting only particular columns:
  • Just specify the desired columns after the table name

  COPY |||tablename||| (|||column₁|||, |||column₂|||, |||column₃|||)
  TO |||full path to file|||
  WITH ( |||import options||| );

Exportings Queries

• Queries return a table, so you can also export that!
  • Embed the query instead of a named table

  COPY (
    SELECT |||col₁|||, |||col₂||| FROM |||tablename|||
    ORDER BY |||col₂|||
    )
  TO |||full path to file|||
  WITH ( |||import options||| );

Some Commentary

• Like many programming languages, SQL has the concept of a comment, which is a piece of text that is entirely ignored by the system when running commands
• In SQL, you can comment a single line at a time using double dash: --
• To block comment, you begin with a /* and end with a */

-- This is a comment
SELECT *
FROM |||table_name||| -- this is my table name
ORDER BY |||column|||;/* this
is all ignored */