Quartz 5

SQL Data Types

Properties1
tagsdatabase/postgres, learningplatform/coursera, scriptinglanguage/sql

3 min read

SQL Data Types

There are primarily few data types in most Database

  • Text Fields (Small and Large)
  • Binary Fields (Small and Large)
  • Numeric Fields
  • AUTO_INCREMENT Fields

String Fields

  • CHAR(N) - allocates entire space for strings whose space is known
  • VARCHAR(N) - allocates a variable amount of space depending on the data length (less space) use VARCHAR when you do not know how long a attribute can be
  • TEXT - have a character set - paragraphs or HTML pages it is of varying length Generally cannot be used with Indexing and Sorting and only limited to a prefix, used with a LIKE clause, it is useful for when you have stuff like blogs and pages.

These all have Character Sets such as ASCII

Binary Fields

  • Character - 8 - 32 bits of information based on character set.
  • Byte - 8 Bits of information - BYTE(N) up to 255 bytes

We can use these where it is not necessary to know the character set or needs to be sorted or indexed, such as storing small images and other forms of data

Integer Fields

These are very efficient, takes little storage and are easy to process for the CPU’s

  • SMALLINT - value varies from -32768 to + 32768
  • INTEGER - 2 Billion
  • BIGINT - 10^8 is the upper value

Floating Fields

These can represent a wide range of numbers but the accuracy is limited

  • REAL - 32Bit with 7 digits of accuracy
  • DOUBLE - 64Bit with 14 digits of accuracy
  • NUMERIC(ACCURACY, DECIMAL) - specified digits of accuracy after the decimal point

Dates

  • TIMESTAMP - YYYY-MM-DD HH:MM:SS
  • DATE - YYYY-MM-DD
  • TIME HH:MM:SS
  • TIMESTAMPTZ - Timestamp with Time Zone
  • Built-In PostgreSQL function NOW() uses the default TimeZone to generate the default Time
    we can use queries to change the timezone as we fetch the data.

It is recommended to store all the TIMESTAMPS with timezone and preferably save all of them in UTC and convert the TIMEZONE during retrieval

Date Casting

We can use keyword CAST to ask PostgreSQL to convert the data from one type to another.

SELECT NOW()::DATE, CAST(NOW() AS DATE), CAST(NOW() AS TIME);

The above example shows a few ways to convert the NOW() timestamp to various other things

Intervals

Using PostgreSQL we can also use Intervals in our queries to query the data within and between dates.

SELECT NOW(), NOW() - INTERVAL '2 Days', (NOW() - INTERVAL '2 Days')::DATE;

There is Interval language to mention the value/parameter in the query. Interval Input - PostgreSQL here we can check the exact syntax

Functions

There are also some functions that are available at our disposal such as DATE_TRUNC() which allows to omit certain data from the time such as, we can use DATE_TRUNC('day',NOW()) to just return the date from the NOW() Timestamp.

SELECT id, content, created_at 
FROM comment
WHERE created_at >= DATE_TRUNC('day',NOW())
AND created_at < DATE_TRUNC('day',NOW() + INTERVAL '1 day');

In the above example we are able to use the function to just extract the date from the NOW() Timestamp.

Graph View