prestoの日付関数

はじめに

prestoの日付関数の使い方がドキュメントを読んでもよくわからないところがあったので試しました。

環境

試した環境はTreasure Dataです。

presto 日付時刻関数

interval

intervalを使って加減算が可能。

SELECT date '2021-04-21' + interval '2' day
--> 2021-03-23
SELECT time '01:00' + interval '3' hour
--> 04:00:00.000
SELECT timestamp '2021-03-21 01:00' + interval '29' hour
--> 2021-03-22 06:00:00.000
SELECT timestamp '2021-01-31 01:00' + interval '1' month
--> 2021-02-28 01:00:00.000
SELECT interval '2' day + interval '3' hour
--> 2 03:00:00.000
SELECT interval '3' year + interval '5' month
--> 3-5
SELECT interval '3' year + interval '12' month
--> 4-0
SELECT date '2021-04-21' - interval '2' day
--> 2021-04-19

AT TIME ZONE

(戻り値: timestamp with time zone)

タイムゾーンで日時変換。こんなのあるんですね。

SELECT timestamp '2021-4-21 01:00 UTC' AT TIME ZONE 'Asia/Tokyo';
--> 2021-04-21 10:00:00.000 Asia/Tokyo

current_date

(戻り値: date)

現在日付。now()を使いがち。

SELECT current_date;
--> 2021-04-21

current_time

(戻り値: timestamp with time zone)

現在時刻。now()を使いがち。

SELECT current_time;
--> 09:43:05.856 UTC

current_timestamp

(戻り値: timestamp with time zone)

現在日時。now()はこの関数のエイリアスなので結果はnow()と同じ。

SELECT current_timestamp;
--> 2021-04-21 09:45:05.441 UTC

current_timezone

(戻り値: varchar)

現在のタイムゾーン？！

SELECT current_timezone();
--> UTC

date

(戻り値: date)

CAST('2021-04-21' as date)と同じく、文字列をdate型にCASTする。

SELECT date('2021-04-21')
--> 2021-04-21

from_iso8601_timestamp

(戻り値: timestamp with time zone)

CAST(x AS date)のエイリアス。エイリアスが長すぎて使わないでしょ、これ。

SELECT from_iso8601_timestamp('2021-04-21T18:59:20+09:00')
--> 2021-04-21 18:59:20.000 +09:00

from_iso8601_date

(戻り値: date)

ISO 8601フォーマットの文字列をdate型に変換する。

SELECT from_iso8601_date('2021-04-21')
--> 2021-04-21

from_unixtime

(戻り値: timestamp[with time zone])

UNIXTIMEからタイムスタンプ型に変換して返​します。よく使いそうです。

SELECT from_unixtime(1618989032)
--> 2021-04-21 07:10:32.000
SELECT from_unixtime(1618989032,'Asia/Tokyo')
--> 2021-04-21 16:10:32.000 Asia/Tokyo
SELECT from_unixtime(1618989032,9,0)
--> 2021-04-21 16:10:32.000 +09:00

localtime

(戻り値: time)

クエリ開始時の現在の時刻をtime型で返します。

SELECT localtime
--> 10:22:33.320

localtimestamp

(戻り値: timestamp)

クエリ開始時の現在の日時をタイムスタンプ型で返します。

SELECT localtimestamp
--> 2021-04-21 10:21:38.901

now

(戻り値: timestamp with time zone)

current_timestampのエイリアス。

SELECT now()
--> 2021-04-21 10:23:09.737 UTC

to_iso8601

(戻り値: varchar)

ISO8601文字列としてフォーマットする。

SELECT to_iso8601(date '2021-04-21')
--> 2021-04-21
SELECT to_iso8601(timestamp '2021-04-21 10:23:09')
--> 2021-04-21T10:23:09.000Z
SELECT to_iso8601(timestamp '2021-04-21 10:23:09.737 UTC')
--> 2021-04-21T10:23:09.737Z

to_milliseconds

(戻り値: bigint)

interval day to secondをミリ秒に変換する。使い道があまり浮かばない。

SELECT to_milliseconds(interval '1' second)
--> 1000
SELECT to_milliseconds(interval '2' day)
--> 172800000
SELECT to_milliseconds(parse_duration('1.824d'))
--> 157593600

to_unixtime

(戻り値: double)

タイムスタンプ型をUNIXTIMEで返​​す。double型なので、小数点まで出力される。

SELECT to_unixtime(timestamp '2021-04-21 09:00:00')
--> 1618995600.0

date_trunc

(戻り値: timestamp)

タイムスタンプ型の日時を第1引数に指定した単位で切り捨てる。

SELECT date_trunc('second',timestamp '2021-04-21 10:23:09.737')
--> 2021-04-21 10:23:09.000
SELECT date_trunc('minute',timestamp '2021-04-21 10:23:09.737')
--> 2021-04-21 10:23:00.000
SELECT date_trunc('hour',timestamp '2021-04-21 10:23:09.737')
--> 2021-04-21 10:00:00.000
SELECT date_trunc('day',timestamp '2021-04-21 10:23:09.737')
--> 2021-04-21 00:00:00.000
SELECT date_trunc('week',timestamp '2021-04-21 10:23:09.737')
--> 2021-04-19 00:00:00.000
SELECT date_trunc('quarter',timestamp '2021-04-21 10:23:09.737')
--> 2021-04-01 00:00:00.000
SELECT date_trunc('year',timestamp '2021-04-21 10:23:09.737')
--> 2021-01-01 00:00:00.000

date_add

（戻り値： timestamp）

第1引数で指定した単位で、第2引数の値を第3引数のタイムスタンプ型に追加する。第2引数はマイナス値でも可能。
よく使うかも。

SELECT date_add('millisecond',1,timestamp '2021-04-21 10:23:09.737')
--> 2021-04-21 10:23:09.738
SELECT date_add('second',1,timestamp '2021-04-21 10:23:09.737')
--> 2021-04-21 10:23:10.737
SELECT date_add('minute',1,timestamp '2021-04-21 10:23:09.737')
--> 2021-04-21 10:24:09.737
SELECT date_add('hour',1,timestamp '2021-04-21 10:23:09.737')
--> 2021-04-21 11:23:09.737
SELECT date_add('day',1,timestamp '2021-04-21 10:23:09.737')
--> 2021-04-22 10:23:09.737
SELECT date_add('week',1,timestamp '2021-04-21 10:23:09.737')
--> 2021-04-28 10:23:09.737
SELECT date_add('month',1,timestamp '2021-04-21 10:23:09.737')
--> 2021-05-21 10:23:09.737
SELECT date_add('quarter',1,timestamp '2021-04-21 10:23:09.737')
--> 2021-07-21 10:23:09.737
SELECT date_add('year',1,timestamp '2021-04-21 10:23:09.737')
--> 2022-04-21 10:23:09.737
SELECT date_add('day',-1,timestamp '2021-03-01 10:23:09.737')
--> 2021-02-28 10:23:09.737

date_diff

（戻り値： bigint）

第2引数の日時と第3引数の日時の差分を第1引数で指定した単位で返す。
よく使うかも。

SELECT date_diff('millisecond',timestamp '2021-04-21 10:23:09.737',timestamp '2021-04-22 10:23:09.737')
--> 86400000
SELECT date_diff('second',timestamp '2021-04-21 10:23:09.737',timestamp '2021-04-22 10:23:09.737')
--> 86400
SELECT date_diff('minute',timestamp '2021-04-21 10:23:09.737',timestamp '2021-04-22 10:23:09.737')
--> 1440
SELECT date_diff('hour',timestamp '2021-04-21 10:23:09.737',timestamp '2021-04-22 10:23:09.737')
--> 24
SELECT date_diff('day',timestamp '2021-04-21 10:23:09.737',timestamp '2021-04-22 10:23:09.737')
--> 1
SELECT date_diff('week',timestamp '2021-04-01 10:23:09.737',timestamp '2021-04-22 10:23:09.737')
--> 3
SELECT date_diff('month',timestamp '2021-01-01 10:23:09.737',timestamp '2021-04-22 10:23:09.737')
--> 3  --単純に月の差分
SELECT date_diff('quarter',timestamp '2021-01-01 10:23:09.737',timestamp '2021-04-22 10:23:09.737')
--> 1  --第1クオーター(1月)と第2クオーター(4月)の差
SELECT date_diff('year',timestamp '2020-04-22 10:23:09.737',timestamp '2021-04-22 10:23:09.736')
--> 0  --1年未満(0.001秒足りない)

parse_duration

（戻り値： interval day to second）

指定した日時文字列をintercalにパースする。
引数の文字列はドキュメントを参照。（バージョン違いますが同じです。）https://prestodb.io/docs/current/functions/datetime.html?highlight=date#duration-function
後半のdは便利かも。

SELECT parse_duration('1000000ns')
--> 0 00:00:00.001
SELECT parse_duration('1000us')
--> 0 00:00:00.001
SELECT parse_duration('1000ms')
--> 0 00:00:01.000
SELECT parse_duration('1.15s')
--> 0 00:00:01.150
SELECT parse_duration('1.8m')
--> 0 00:01:48.000
SELECT parse_duration('1.2h')
--> 0 01:12:00.000
SELECT parse_duration('1.5d')
--> 1 12:00:00.000
SELECT parse_duration('1.824d')
--> 1 19:46:33.600

date_format

（戻り値： varchar）

タイムスタンプ型日時を日付文字列にフォーマット。この関数は、MySQLの日付時刻フォーマットを使用する。
引数のフォーマットはドキュメントを参照。https://prestodb.io/docs/current/functions/datetime.html?highlight=date#mysql-date-functions

SELECT date_format(timestamp '2021-05-13 16:23:54.123', '%Y-%m-%d %W %H:%i:%s')
--> 2021-05-13 Thursday 16:23:54
SELECT date_format(timestamp '2021-05-01 16:23:54.123', '%y-%c-%e %l:%i:%s %p')
--> 21-5-1 4:23:54 PM

date_parse

（戻り値： timestamp）

日付文字列をタイムスタンプ型日時に変換する。日付文字列のフォーマットを第2引数に記述する。この関数は、MySQLの日付時刻フォーマットを使用する。
引数のフォーマットはドキュメントを参照。https://prestodb.io/docs/current/functions/datetime.html?highlight=date#mysql-date-functions

SELECT date_parse('05/13/21 1:23','%m/%d/%y %H:%i')
--> 2021-05-13 01:23:00.000

format_datetime

（戻り値： varchar）

タイムスタンプ型日時を日付文字列にフォーマット。この関数は、Javaの日付時刻フォーマットを使用する。date_formatより、こっちの方が馴染む。
引数のフォーマットはこちら。http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html

SELECT format_datetime(timestamp '2021-05-13 16:23:54.123', 'YYYY-MM-dd EEEE HH:mm:ss')
--> 2021-05-13 Thursday 16:23:54
SELECT format_datetime(timestamp '2021-05-13 16:23:54.123', 'YY-M-d h:m:s a')
--> 21-5-13 4:23:54 PM

parse_datetime

（戻り値： timestamp）

日付文字列をタイムスタンプ型日時に変換する。日付文字列のフォーマットを第2引数に記述する。この関数は、Javaの日付時刻フォーマットを使用する。
date_parseと違う点があり、こちらはタイムゾーン付きで返ってきます。
引数のフォーマットはこちら。http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html

SELECT parse_datetime('05/13/21 1:23','MM/dd/YY HH:mm')
--> 2021-05-13 01:23:00.000 UTC

extract

（戻り値： bigint）

文字列から指定した日時の値を取得し返す。
引数はドキュメントを参照。
https://prestodb.io/docs/current/functions/datetime.html?highlight=date#extraction-function

SELECT extract(YEAR FROM TIMESTAMP '2021-05-13 01:23:00.000 UTC')
--> 2021
SELECT extract(MONTH FROM DATE '2021-05-13')
--> 5

day

（戻り値： bigint）

引数の日付の日を返す。extractより、こっちの方が使う気がする。

SELECT day(date '2021-05-13')
--> 13

day_of_month

（戻り値： bigint）

day()のエイリアスとのことで、day()と同じ結果になる。

SELECT day_of_month(date '2021-05-13')
--> 13

day_of_week

（戻り値： bigint）

引数の日付のISO曜日を返す。ISO 8601の規格では、月曜日から日曜日を1から7の数字で表すとのこと。

SELECT day_of_week(date '2021-05-13')
--> 4 #月火水木金土日=1234567

day_of_year

（戻り値： bigint）

引数の日付が1年のうちの何日目かを返す。

SELECT day_of_year(date '2021-05-13')
--> 136

doy

（戻り値： bigint）

day_of_year()のエイリアス。

SELECT doy(date '2021-05-13')
--> 136

hour

（戻り値： bigint）

引数の日時の時(0〜23)を返す。

SELECT hour(timestamp '2021-05-13 16:00')
--> 16

millisecond

（戻り値： bigint）

引数の日時のミリ秒を返す。

SELECT millisecond(timestamp '2021-05-13 16:37:10.023')
--> 23

minute

（戻り値： bigint）

引数の日時の分を返す。

SELECT minute(timestamp '2021-05-13 16:02:10.023')
--> 2

month

（戻り値： bigint）

引数の日時の月を返す。

SELECT month(timestamp '2021-05-13 16:02:10.023')
--> 5

quarter

（戻り値： bigint）

引数の日付のその年の四半期を返す。第一四半期(1)〜第四四半期(4)。

SELECT quarter(timestamp '2021-05-13 16:02:10.023')
--> 2

second

（戻り値： bigint）

引数の日時の秒を秒で返す。

SELECT second(timestamp '2021-05-13 16:02:10.023')
--> 10

timezone_hour

（戻り値： bigint）

引数の日時のタイムゾーンの時差を返す。

SELECT timezone_hour(timestamp '2021-05-13 16:02:10.023 Asia/Tokyo')
--> 9

week

（戻り値： bigint）

引数の日付のその年の何週目か返す。週はISO 8601で定義されている1〜53週。

SELECT week(timestamp '2021-05-13 16:02:10')
--> 19

week_of_year

（戻り値： bigint）

weekのエイリアスなので、weekと同じ値を返す。

SELECT week_of_year(timestamp '2021-05-13 16:02:10')
--> 19

year

（戻り値： bigint）

引数の日付の年を返す。

SELECT year(timestamp '2021-05-13 16:02:10')
--> 2021

year_of_week

（戻り値： bigint）

使い道と使い方がわからないです。使い方が間違ってるかもしれません。

SELECT year_of_week(date '2021-05-13')
--> 2021

yow

（戻り値： bigint）

year_of_weekのエイリアス。

SELECT yow(date '2021-05-13')
--> 2021

参考リンク