[Edit]

PostgreSQL - extract day of week from date/time value

1 contributors

2 contributions

0 discussions

0 points

Created by:

Wade

562

In this article, we would like to show you how to extract day of week from DATE, TIMESTAMP or TIME in PostgreSQL. Returns a result from 0 (Sunday) to 6 (Saturday).

Quick solution:

xxxxxxxxxx
 
SELECT EXTRACT('dow' FROM "column_name")
FROM "table_name";

PostgreSQL - extract day of week from timestamp/interval — Table used in the example - HeidiSQL

Note:

At the end of this article you can find database preparation SQL queries.

Edit

Practical example

Edit

In this example, we will only select days of week from each record in the event_datetime column.

Query:

xxxxxxxxxx
 
SELECT EXTRACT('dow' FROM "event_timestamp") AS "day of week"
FROM "events";

Output:

Database preparation

Edit

create_tables.sql file:

xxxxxxxxxx
 
CREATE TABLE "events"(
    "id" SERIAL PRIMARY KEY,
    "event_timestamp" TIMESTAMP
);

insert_data.sql file:

xxxxxxxxxx
 
INSERT INTO "events"
    ("event_timestamp") 
VALUES 
    ('2021-01-01 11:41:31'),
    ('2021-01-02 12:42:32'),
    ('2021-01-03 13:43:33'),
    ('2021-01-04 14:44:34'),
    ('2021-01-05 15:45:35'),
    ('2021-01-06 16:46:36'),
    ('2021-01-07 17:47:37'),
    ('2021-01-08 18:48:38'),
    ('2021-01-09 19:49:39'),
    ('2021-01-10 20:50:40');