Bigquery | Date / time system query compilation (query reference Japanese translation)

## 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](http://www.cplusplus.com/reference/ctime/strftime/) (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 日本語訳 )](https://qiita.com/Yinaura/items/303d6a563e1625d9f87d)

# About

[About this translattion](https://qiita.com/YumaInaura/items/7f6fd1e9310a6816469a)








<!-- Update From Qiita API -->

# チャットメンバー募集


何か質問、悩み事、相談などあればLINEオープンチャットもご利用ください。

https://line.me/ti/g2/eEPltQ6Tzh3pYAZV8JXKZqc7PJ6L0rpm573dcQ





# Twitter


https://twitter.com/YumaInaura


<!-- Update From Qiita API -->