Bigquery | Date / time system query compilation (query reference Japan
CURRENT_DATE
Returns the current date. The format is year-month-day.
Returns a human-readable string of the current date in the format% Y-% m-% d.
SELECT CURRENT_DATE();
Results: 2013-02-01
CURRENT_TIME
Returns the current time of the server in a readable form. The format is hour-minute-second.
Returns a human-readable string of the server's current time in the format% H:% M:% S.
SELECT CURRENT_TIME();
Results: 01:32:56
CURRENT_TIMESTAMP
Returns the server's current time as a timestamp. The format is year-month-day hour: minute: second.
Returns a TIMESTAMP data type of the server's current time in the format% Y-% m-% d% H:% M:% S.
SELECT CURRENT_TIMESTAMP();結果: 2013-02-01 01:33:35 UTC
DATE (<time stamp>)
Given a timestamp, returns the date. The format is year-month-day.
Returns a human-readable string of a TIMESTAMP data type in the format% Y-% m-% d.
SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));
Results: 2012-10-01
DATE_ADD (<timestamp>, <period>, <period type>)
Add the specified period to the timestamp. "Type of period" can be given year, month, day, hour, minute, second.
If you give a negative "period", that amount will be reduced.
Possible interval_units values include YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. If interval is a negative number, the interval is subtracted from the TIMESTAMP data type.
SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");
Results: 2017-10-01 02:03:04 UTC
SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");
Results: 2007-10-01 02:03:04 UTC
DATEDIFF (<time stamp 1>, <time stamp 2>)
Given two timestamps, returns the date difference as an integer.
Returns the number of days between two TIMESTAMP data types.
SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));
Results: 466
DAY (<time stamp>)
Given a timestamp, returns the day as an integer from 1 to 31.
Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusive.
SELECT DAY(TIMESTAMP('2012-10-02 05:23:48'));
Result: 2
DAYOFWEEK (<time stamp>)
Given a timestamp, it returns "What day of the week". You can get an integer from 1 (Sunday) to 7 (Saturday).
Returns the day of the week of a TIMESTAMP data type as an integer between 1 (Sunday) and 7 (Saturday), inclusive.
SELECT DAYOFWEEK(TIMESTAMP("2012-10-01 02:03:04"));
Result: 2
DAYOFYEAR (<time stamp>)
Given a timestamp, it returns "the day of the year" as an integer from 1 to 366. For example, "1" represents "January 1".
(* There is only 366 for the leap year.)
Returns the day of the year of a TIMESTAMP data type as an integer between 1 and 366, inclusive. The integer 1 refers to January 1.
SELECT DAYOFYEAR(TIMESTAMP("2012-10-01 02:03:04"));
Results: 275
FORMAT UTC USEC (<\ Unix format timestamp>)
Given a UNIX timestamp, it returns the year-month-day hour: minute: second .uuuuuu.
Returns a human-readable string representation of a UNIX timestamp in the format YYYY-MM-DD HH: MM: SS.uuuuuu.
SELECT FORMAT_UTC_USEC(1274259481071200);
Results: 2010-05-19 08: 58: 01.071200
HOUR (<time stamp>)
Given a timestamp, returns the hour as an integer from 0 to 23.
Returns the hour of a TIMESTAMP data type as an integer between 0 and 23, inclusive.
SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));
Result: 5
MINUTE (<time stamp>)
Given a timestamp, returns a minute as an integer from 0 to 59.
Returns the minutes of a TIMESTAMP data type as an integer between 0 and 59, inclusive.
SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));
Results: 23
MONTH (<time stamp>)
Returns "Month" as an integer from 0 to 12 giving a timestamp.
Returns the month of a TIMESTAMP data type as an integer between 1 and 12, inclusive.
SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));
Result: 10
MSEC TO TIMESTAMP (<UNIX timestamp>)
Given a millisecond in UNIX timestamp format, returns a timestamp.
Converts a UNIX timestamp in milliseconds to a TIMESTAMP data type.
SELECT MSEC_TO_TIMESTAMP(1349053323000);
Results: 2012-10-01 01:02:03 UTC
SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)
Results: 2012-10-01 01:02:04 UTC
NOW ()
Returns the "UNIX timestamp" in microseconds.
Returns a UNIX timestamp in microseconds.
SELECT NOW();
Results: 1359685811687920
PARSE UTC USEC (<date>)
年-月-日 時:分:秒[.uuuuuu] a date in the format 年-月-日 時:分:秒[.uuuuuu] , it returns microseconds. [.uuuuuu] part can be omitted.
By the way, TIMESTAMP_TO_USEC has the same function, but it receives a timestamp instead of a string.
Date_string must have the format YYYY-MM-DD HH: MM: SS [.uuuuuu]. The fractional part of the second part can be up to 6 digits long or can be omitted.
TIMESTAMP TO USEC is an equivalent function that converts a TIMESTAMP data type argument instead of a date string.
SELECT PARSE_UTC_USEC("2012-10-01 02:03:04");
The result: 1349056984000000
QUARTER (<time stamp>)
Given a timestamp, it returns "Quartery" as an integer from 1 to 4.
Returns the quarter of the year of a TIMESTAMP data type as an integer between 1 and 4, inclusive.
SELECT QUARTER(TIMESTAMP("2012-10-01 02:03:04"));
Result: 4
SEC TO TIMESTAMP (<UNIX timestamp>)
Convert a UNIX-style timestamp to a timestamp.
Converts a UNIX timestamp in seconds to a TIMESTAMP data type.
SELECT SEC_TO_TIMESTAMP(1355968987);
Results: 2012-12-20 02:03:07 UTC
SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));
Results: 2012-12-20 02:03:07 UTC
SECOND (<time stamp>)
Given a timestamp, returns seconds as an integer from 0 to 59. However, if a leap second is included, it is an integer from 0 to 60.
During a leap second, the integer range is between 0 and 60, Inclusive. Returns the seconds of a TIMESTAMP data type as an integer between 0 and 59, inclusive.
SELECT SECOND(TIMESTAMP('2012-10-02 05:23:48'));
Results: 48
STRFTIME UTC USEC (<Unix format timestamp>, <Date and time format>)
Returns the date and time in the specified format.
"Date and time format" is a separator like hyphen or slash. And you can use the special characters used in C ++ strftime. (Example:% d represents a day)
. Returns A Human-Readable Date String In The Format Date Format Str Date Format Str Can Include Date-Related Punctuation Characters (Such As / And -) And Special Characters Accepted By The Strftime Function In C Tasutasu (Such As Pasento D For Day Of Month ).
TRANSRATEME:
Use the UTC USEC TO <function name> functions if you plan to group query data by time intervals, such as getting all data for a certain month, because the functions are more efficient.
SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");
Results: 2010-05-19
TIME (<time stamp>)
Given a timestamp, returns a string in the format hours: minutes: seconds.
Returns a human-readable string of a TIMESTAMP data type, in the format% H:% M:% S.
SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));
Result: 02:03:04
TIMESTAMP (<date>)
Given a date as a string, it will be converted to a timestamp.
Convert a date string to a TIMESTAMP data type.
SELECT TIMESTAMP("2012-10-01 01:02:03");
Results: 2012-10-01 01:02:03 UTC
TIMESTAMP TO MSEC (<timestamp>)
Convert a timestamp to UNIX milliseconds.
SELECT TIMESTAMP_TO_MSEC(TIMESTAMP("2012-10-01 01:02:03"));
Results: 1349053323000
TIMESTAMP TO SEC (<timestamp>)
Convert a timestamp to UNIX seconds.
SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));
Results: 1349053323
TIMESTAMP TO USEC (<time stamp>)
PARSE UTC USEC is an equivalent function that converts a data string argument instead of a TIMESTAMP data type.
SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));
Results: 1349053323000000
USEC TO TIMESTAMP (<expr>)
Converts a UNIX timestamp in microseconds to a TIMESTAMP data type.
SELECT USEC_TO_TIMESTAMP(1349053323000000);
Results: 2012-10-01 01:02:03 UTC
SELECT USEC TO TIMESTAMP (1349053323000000 + 1000000)
Results: 2012-10-01 01:02:04 UTC
UTC USEC TO_DAY (<timestamp in UNIX format>)
TRANSRATEME:
Converts milliseconds in UNIX timestamps to those of the beginning of the day. For example, given a UNIX timestamp of May 19th at 08:58 , it May 19th at 08:58 a UNIX timestamp of May 19th at 00:00 (midnight).
For example, if unix_timestamp occurs on May 19th at 08:58, this function returns a UNIX timestamp for May 19th at 00:00 (midnight). Shifts a UNIX timestamp in microseconds to the beginning of the day occurs in.
SELECT UTC_USEC_TO_DAY(1274259481071200);
Results: 127422270000000
UTC USEC TO_HOUR (<UNIX timestamp>)
Given a UNIX timestamp, returns the first microsecond of that "hour". For example, if the given UNIX timestamp is 8:58, it will return 8:00 microseconds on the same day.
For example, if unix_timestamp occurs at 08: 58, this function returns a UNIX timestamp for 08: 00 on the same day. Shifts a UNIX timestamp in microseconds to the beginning of the hour occurs in.
SELECT UTC_USEC_TO_HOUR(1274259481071200);
Results: 1274256000000000
UTC USEC TO_MONTH (<UNIX timestamp>)
Given UNIX timestamps in microseconds. Returns the "first microsecond of the month".
For example, if unix_timestamp occurs on March 19th, this function returns a UNIX timestamp for March 1st of the same year. Shifts a UNIX timestamp in microseconds to the beginning of the months occurs in.
SELECT UTC_USEC_TO_MONTH(1274259481071200);
Results: 1272672000000000
UTC USEC TO_WEEK (<UNIX timestamp>, <day of the week>)
Returns a UNIX timestamp in microseconds.
First is the "UNIX timestamp" in microseconds. Second, give the day of the week between 0 (Sunday) and 6 (Saturday).
For example, giving a Unix timestamp on Friday, April 11, 2008. If you give 2 (Tuesday) on the day of the week.
Returns the UNIX microseconds of April 18, 2008 (Tuesday).
This function takes two arguments: a UNIX timestamp in microseconds, and a day of the week from 0 (Sunday) to 6 (Saturday).
For example, if unix timestamp occurs on Friday, 2008-04-11, and you set day of week to 2 (Tuesday), the function returns a UNIX timestamp for Tuesday, 2008-04-08.
SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;
Results: 1207612800000000
UTC USEC TO_YEAR (<UNIX timestamp>)
Given a UNIX timestamp, returns the first UNIX timestamp of the year in milliseconds.
For example, if the UNIX timestamp is for 2010, it will return 1274259481071200 . This is the first timestamp of 2010-01-01 00:00 .
For example, if unix timestamp occurs in 2010, the function returns 1274259481071200, the microsecond representation of 2010-01-01 00:00. Returns a UNIX timestamp in microseconds that represent the year of the unix timestamp argument .
SELECT UTC_USEC_TO_YEAR(1274259481071200);
Results: 1262304000000000
WEEK (<time stamp>)
Given a timestamp, it returns "Week" as an integer from 1 to 53.
This "week" starts on Sunday.
So, for example, if January 1 is non-Sunday, the week of "1" has less than 7 days. In this case, the first Sunday will be included in the "2" week.
Weeks begin on Sunday, so if January 1 is on a day other than Sunday, week 1 has more than 7 days and the first Sunday of the year is the first day of week 2.
SELECT WEEK(TIMESTAMP('2014-12-31'));
Results: 53
YEAR (<time stamp>)
Given a timestamp returns the year.
Returns the year of a TIMESTAMP data type.
SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));
The result: 2012
Supplement
- It is a translation as of April 2016
- A translation of the official guide (https://cloud.google.com/bigquery/query-reference).
- It's somewhat sensible.
- Please send an edit quest if you make a mistake.
Original by
Bigquery | 日付・時間系のクエリまとめ ( query reference 日本語訳 )
About
チャットメンバー募集
何か質問、悩み事、相談などあればLINEオープンチャットもご利用ください。
公開日時
2019-04-16
Discussion