Database Part 2 – Section 1

[Datatypes in MSSQL]

[Datatypes in PostgresSQL]

MSSQL Date and time data type precision

-- Store dates and times in SQL Server
CREATE TABLE TimeExperiment (
    a datetimeoffset, --stores timezone, stores time down to 100 nanoseconds
    b datetimeoffset(0) -- round times to the nearest second
);

INSERT INTO TimeExperiment
    VALUES
        (getdate(), getdate())
;

SELECT * FROM TimeExperiment;

MSSQL Date Formats

-- Dates and time values can be entered in a variety of ways
INSERT INTO TimeExperiment (a)
    VALUES
        ('2020-01-02'), -- Standard date format
        ('Jan 2, 2020'), -- Can also use text abbreviations
        ('2 January, 2020 14:25'), -- Combine date and time
        ('2 Jan, 2020 2:25 PM') -- Specify PM when entering afternoon times in 12 hour format
;

SELECT * FROM TimeExperiment;

PostgresSQL Date and time data type precision

-- Store dates and times in PostgreSQL
CREATE TABLE TimeExperiment (
    a timestamp with time zone, -- store to 100 nanoseconds
    b timestamp (0) with time zone -- store to seconds
);

INSERT INTO TimeExperiment
    VALUES
        (now(), now())
;

SELECT * FROM TimeExperiment;

PostgresSQL Date Formats

-- Dates and time values can be entered in a variety of ways
INSERT INTO TimeExperiment (a)
    VALUES
        ('2020-01-02'), -- Standard date format
        ('Jan 2, 2020'), -- Can also use text abbreviations
        ('2 January, 2020 14:25'), -- Combine date and time
        ('2 Jan, 2020 2:25 PM') -- Specify PM when entering afternoon times in 12 hour format
;

SELECT * FROM TimeExperiment;