[Edit]

PostgreSQL - extract day of year from date/time value

1 contributors

2 contributions

0 discussions

0 points

Created by:

Tehya-Blanchard

474

In this article, we would like to show you how to extract day of year (1 to 366) from DATE, TIMESTAMP, TIME or INTERVAL in PostgreSQL.

Quick solution:

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

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

Note:

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

Practical example

Edit

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

Query:

xxxxxxxxxx
 
SELECT EXTRACT('doy' FROM "event_timestamp") AS "day of year"
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-02-01 18:48:38'),
    ('2021-02-09 19:49:39'),
    ('2021-02-10 20:50:40');