Tarih ve saat verisini toplama ve çıkarma

PostgreSQL ile Zaman Serisi Analizi

Jasmin Ludolf

Content Developer, DataCamp

Zaman farkları

AGE() : ilk argümandan ikincisini veya geçerli tarihi çıkarır
AGE(timestamp, timestamp) veya AGE(timestamp)

SELECT
AGE('2018-01-02','2017-01-01') AS "2017",
AGE('2021-01-05','2020-01-01') AS "2020";

|2017        |2020         |
|------------|-------------|
|1 year 1 day|1 year 4 days|

Farkları yanlış yorumlamak

SELECT 
AGE('2017-12-31','2017-01-01') AS "2017",
AGE('2018-12-31','2018-01-01') AS "2018",
AGE('2019-12-31','2019-01-01') AS "2019",
AGE('2020-12-31','2020-01-01') AS "2020";

|2017           |2018           |2019           |2020           |
|---------------|---------------|---------------|---------------|
|11 mons 30 days|11 mons 30 days|11 mons 30 days|11 mons 30 days|

Çıkarma operatörü

SELECT
'2018-01-01'::DATE - '2017-01-01'::DATE AS "2017",
'2019-01-01'::DATE - '2018-01-01'::DATE AS "2018",
'2020-01-01'::DATE - '2019-01-01'::DATE AS "2019",
'2021-01-01'::DATE - '2020-01-01'::DATE AS "2020";

|2017|2018|2019|2020|
|----|----|----|----|
| 365| 365| 365| 366|

Çıkarma operatörünü kullanma

- : çıkarma operatörü
INTERVAL veri türü döndürür
INTERVAL, bir zaman süresini saklamayı ve işlemeyi sağlar

SELECT
    '2021-01-01 00:03:00'::TIMESTAMP - '2021-01-01 00:01:30'::TIMESTAMP 
    AS interval;

|interval|
|--------|
|00:01:30|

Zaman aralıkları

WITH line_324 AS (
    SELECT station, arrival_time 
    FROM train_schedule 
    WHERE train_id=324 )
SELECT hillsdale.arrival_time - millbrae.arrival_time AS diff
FROM line_324 AS millbrae, line_324 AS hillsdale
WHERE millbrae.station='Millbrae' 
AND hillsdale.station='Hillsdale';

CTE: Common Table Expression, tek sorgu için geçici tablo tanımlar

¹ https://www.caltrain.com/schedules/weekdaytimetable.html

Zaman aralıkları

WITH line_324 AS (
    SELECT station, arrival_time 
    FROM train_schedule 
    WHERE train_id=324 )
SELECT hillsdale.arrival_time - millbrae.arrival_time AS diff
FROM line_324 AS millbrae, line_324 AS hillsdale
WHERE millbrae.station='Millbrae' 
AND hillsdale.station='Hillsdale';

|diff    |
|--------|
|00:08:00|

¹ https://www.caltrain.com/schedules/weekdaytimetable.html

Bir aralığı çıkarmak

SELECT 
    '2020-02-01'::DATE - INTERVAL '1 month' AS "1 ay önce";

|1 ay önce          |
|-------------------|
|2020-01-01 00:00:00|

Bir aralığı belirli bir zaman birimine dönüştürme

EXTRACT(epoch FROM start_time - end_time)

SELECT EXTRACT(epoch FROM
    ('2021-01-01 00:03:00'::TIMESTAMP - '2021-01-01 00:01:30'::TIMESTAMP))

    AS seconds;

|seconds|
|-------|
|   90.0|

Bir aralığı belirli bir zaman birimine dönüştürme

SELECT (EXTRACT(epoch FROM
    ('2021-01-01 00:01:00'::TIMESTAMP - '2020-12-31 23:59:30'::TIMESTAMP)))
    / 60 AS minutes;

|minutes|
|-------|
|    1.5|

Zaman ekleme

SELECT '2019-02-01'::DATE + INTERVAL '28 days' AS "28 gün sonra";

|28 gün sonra       |
|-------------------|
|2019-03-01 00:00:00|

SELECT '2020-02-01'::DATE + INTERVAL '28 days' AS "28 gün sonra";

|28 gün sonra       |
|-------------------|
|2020-02-29 00:00:00|

Bir tarihe ay ekleme

SELECT '2019-02-01'::DATE + INTERVAL '1 month' AS "1 ay sonra";

|1 ay sonra         |
|-------------------|
|2019-03-01 00:00:00|

SELECT '2020-02-01'::DATE + INTERVAL '1 month' AS "1 ay sonra";

|1 ay sonra         |
|-------------------|
|2020-03-01 00:00:00|

Hadi pratik yapalım!

PostgreSQL ile Zaman Serisi Analizi