Date and time MySQL functions

Spanner supports the following MySQL date and time functions.You need to implement the MySQL functions in yourSpanner database before you can use them. For more information oninstalling the functions, seeInstall MySQL functions.

Function list

NameSummary
mysql.DATE_FORMATFormats a date as specified.
mysql.DAYAlias for theDAYOFMONTH function. Returns the day of the month (1-31) from aTIMESTAMP value.
mysql.DAYNAMEReturns the name of the weekday.
mysql.DAYOFMONTHReturns the day of the month (1-31).
mysql.DAYOFWEEKReturns the weekday index (1-7) of the input parameter.
mysql.DAYOFYEARReturns the day of the year (1-366).
mysql.FROM_DAYSConverts a day number to a date.
mysql.FROM_UNIXTIMEFormats Unix timestamp as a date.
mysql.HOURReturns the hour.
mysql.MAKEDATECreates aDATE value from a specified year and day of the year.
mysql.MICROSECONDReturns the microseconds from the input parameter.
mysql.MINUTEReturns the minute from the input parameter.
mysql.MONTHReturns the month from the date passed.
mysql.MONTHNAMEReturns the name of the month.
mysql.PERIOD_ADDAdds a specified number of months to a period of time.
mysql.PERIOD_DIFFReturns the number of months between two periods.
mysql.QUARTERReturns the quarter from a date input parameter.
mysql.SECONDReturns the second (0-59).
mysql.STR_TO_DATEConverts a string to a date.
mysql.SYSDATEReturns theTIMESTAMP at which the query statement that contains this function started to run.
mysql.TIMEExtracts the time portion of the expression passed.
mysql.TO_DAYSReturns the date input parameter converted to days.
mysql.TO_SECONDSReturns the date or datetime input parameter converted to seconds since year zero.
mysql.UNIX_TIMESTAMPReturns a Unix timestamp.
mysql.UTC_DATEReturns the current UTC date.
mysql.WEEKReturns the week number (1-53).
mysql.WEEKDAYReturns the weekday index (0-6).
mysql.WEEKOFYEARReturns the calendar week of the date (1-53).
mysql.YEARReturns the year.

mysql.DATE_FORMAT

mysql.DATE_FORMAT(timestamp_expression,format_string)

Description

Formats aTIMESTAMP value according to a specified format string.

This function supports the following arguments:

  • timestamp_expression: TheTIMESTAMP value to format.
  • format_string: ASTRING value that containsformat elementsto use withtimestamp_expression.

Return data type

STRING

Differences from MySQL

This function accepts onlyTIMESTAMP values, while the MySQL version also acceptsDATE values depending on the format string. This implementation also supports asmaller subset of the format specifiers available in conventional MySQL.

Limitations

  • The following format specifiers are not supported:%c, %D, %f, %h, %i, %M, %r, %s, %u, %V, %W, %X, %x.
  • When you apply time-related format specifiers to aDATE object, this functionignores them. In contrast, MySQL substitutes values from a default time.

Example

The following example formats aTIMESTAMP value:

SELECTmysql.DATE_FORMAT(TIMESTAMP'2023-10-27','%Y-%d-%m')asformatted_date;/*+----------------+| formatted_date |+----------------+| 2023-27-10     |+----------------+*/

mysql.DAY

mysql.DAY(timestamp_expression)

Description

Returns the day of the month for aTIMESTAMP value, from 1 to 31. This is analias forDAYOFMONTH.

This function supports the following argument:

  • timestamp_expression: TheTIMESTAMP value.

Return data type

INT64

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values.

Limitations

  • If you provide an invalid timestamp, this function returns an error. Incontrast, MySQL returnsNULL.
  • This function doesn't support the "zero date" (0000-00-00). Providing thisvalue causes an error, while MySQL returnsNULL.

Example

The following example gets the day of the month from aTIMESTAMP value:

SELECTmysql.DAY(TIMESTAMP'2025-05-30')ASday_of_month;/*+--------------+| day_of_month |+--------------+| 30           |+--------------+*/

mysql.DAYNAME

mysql.DAYNAME(timestamp_expression)

Description

Returns the full name of the weekday in English for a givenTIMESTAMP value.

This function supports the following argument:

  • timestamp_expression: TheTIMESTAMP value from which to extract the weekday name.

Return data type

STRING

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values. In MySQL, the output language is controlled by thelc_time_names system variable; in GoogleSQL, the output is always in English.

Limitations

This function has no direct limitations. However, if you provide the timestamp as astring literal that is not a valid timestamp, this function returns an error. TheMySQL version returnsNULL.

Example

The following example returns the name of the weekday from aTIMESTAMP value:

SELECTmysql.DAYNAME(TIMESTAMP'2025-05-30')asday_name;/*+----------+| day_name |+----------+| Friday   |+----------+*/

mysql.DAYOFMONTH

mysql.DAYOFMONTH(timestamp_expression)

Description

Returns the day of the month for aTIMESTAMP value, from 1 to 31.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

INT64

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values.

Limitations

If you provide an invalid timestamp, this function returns an error. In contrast,MySQL returnsNULL.

Example

The following example returns the day of the month from aTIMESTAMP value:

SELECTmysql.DAYOFMONTH(TIMESTAMP'2025-05-30')asdayofmonth;/*+------------------+| dayofmonth       |+------------------+| 30               |+------------------+*/

mysql.DAYOFWEEK

mysql.DAYOFWEEK(timestamp_expression)

Description

Returns the weekday index for aTIMESTAMP value. The index uses Sunday as thefirst day of the week (Sunday = 1, Saturday = 7).

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

INT64

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values.

Limitations

This function has no direct limitations. However, if you provide the timestamp as astring literal that is not a valid timestamp, this function returns an error. TheMySQL version returnsNULL.

Example

The following example returns the weekday index for a given timestamp:

SELECTmysql.DAYOFWEEK(TIMESTAMP'2025-05-30')ASday_of_week;/*+-------------+| day_of_week |+-------------+| 6           |+-------------+*/

mysql.DAYOFYEAR

mysql.DAYOFYEAR(timestamp_expression)

Description

Returns the day of the year for aTIMESTAMP value, from 1 to 366.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

INT64

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values.

Limitations

This function has no direct limitations. However, if you provide the timestamp as astring literal that is not a valid timestamp, this function returns an error. TheMySQL version returnsNULL.

Example

The following example returns the day of the year from aTIMESTAMP value:

SELECTmysql.DAYOFYEAR(TIMESTAMP'2025-05-30')ASday_of_year;/*+-------------+| day_of_year |+-------------+| 150         |+-------------+*/

mysql.FROM_DAYS

mysql.FROM_DAYS(day_number)

Description

Converts anINT64 day number into aDATE value.

This function supports the following argument:

  • day_number: The number of days.

Return data type

DATE

Differences from MySQL

  • This function does not support dates before0001-01-01.

  • Dates that precede the Gregorian calendar(1582), might vary from the MySQL version.

Example

The following example converts a day number to aDATE value:

SELECTmysql.FROM_DAYS(739765)ASdate_from_days;/*+----------------+| date_from_days |+----------------+| 2025-05-29     |+----------------+*/

mysql.FROM_UNIXTIME

mysql.FROM_UNIXTIME(unix_timestamp)

Description

Converts a Unix timestamp (seconds since the epoch) into aTIMESTAMP value.

This function supports the following argument:

  • unix_timestamp: The number of seconds since the Unix epoch (1970-01-01 00:00:00 UTC).

Return data type

TIMESTAMP

Differences from MySQL

This function supports a wider range of timestamps than the MySQL version,including negative timestamps. The output is always in UTC, while the MySQLversion output depends on the session time zone.

Limitations

This function only supports the single-argument version ofFROM_UNIXTIME.

Example

The following example converts a Unix timestamp to aTIMESTAMP value:

SELECTmysql.FROM_UNIXTIME(1748601000)AStimestamp_from_unix;/*+------------------------+| timestamp_from_unix    |+------------------------+| 2025-05-30 10:30:00+00 |+------------------------+*/

mysql.HOUR

mysql.HOUR(timestamp_expression)

Description

Returns the hour from aTIMESTAMP value, from 0 to 23.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

INT64

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values.

Limitations

If you provide an invalid timestamp, this function returns an error. In contrast,MySQL returnsNULL.

Example

The following example gets the hour from aTIMESTAMP value:

SELECTmysql.HOUR(TIMESTAMP'2025-05-30 14:30:45.123456')ashour;/*+------+| hour |+------+| 14   |+------+*/

mysql.MAKEDATE

mysql.MAKEDATE(year,day_of_year)

Description

Creates aDATE value from a specified year and day of the year. The day of theyear value is from 1 to 366.

This function supports the following argument:

  • year: The year (INT64).
  • day_of_year: The day of the year (INT64).

Return data type

DATE

Example

The following example creates aDATE value from the input parameters provided:

SELECTmysql.MAKEDATE(2025,150)ASdate_from_year_day;/*+--------------------+| date_from_year_day |+--------------------+| 2025-05-30         |+--------------------+*/

mysql.MICROSECOND

mysql.MICROSECOND(timestamp_expression)

Description

Returns the microsecond component from aTIMESTAMP value, from 0 to 999999.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

INT64

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values.

Limitations

If you provide an invalid timestamp, this function returns an error. In contrast,MySQL returnsNULL.

Example

The following example returns the microsecond from aTIMESTAMP value:

SELECTmysql.MICROSECOND(TIMESTAMP'2025-05-30 14:30:45.123456')asmicrosecond;/*+-------------+| microsecond |+-------------+| 123456      |+-------------+*/

mysql.MINUTE

mysql.MINUTE(timestamp_expression)

Description

Returns the minute from aTIMESTAMP value, from 0 to 59.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

INT64

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values.

Limitations

If you provide an invalid timestamp, this function returns an error. In contrast,MySQL returnsNULL.

Example

The following example returns the minute from aTIMESTAMP value:

SELECTmysql.MINUTE(TIMESTAMP'2025-05-30 14:30:45.123456')asminute;/*+--------+| minute |+--------+| 30     |+--------+*/

mysql.MONTH

mysql.MONTH(timestamp_expression)

Description

Returns the month from aTIMESTAMP value, from 1 to 12.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

INT64

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values.

Limitations

If you provide an invalid timestamp, this function returns an error. In contrast,MySQL returnsNULL.

Example

The following example returns the month from aTIMESTAMP value:

SELECTmysql.MONTH(TIMESTAMP'2025-05-30')asmonth_num;/*+-----------+| month_num |+-----------+| 5         |+-----------+*/

mysql.MONTHNAME

mysql.MONTHNAME(timestamp_expression)

Description

Returns the full name of the month in English for aTIMESTAMP value.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

STRING

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values. In MySQL, the output language is controlled by thelc_time_names system variable; in GoogleSQL, the output is always in English.

Limitations

This function has no direct limitations. However, if you provide the timestamp as astring literal that is not a valid timestamp, this function returns an error. TheMySQL version returnsNULL.

Example

The following example returns the month name from aTIMESTAMP value:

SELECTmysql.MONTHNAME(TIMESTAMP'2025-05-30')ASmonth_name;/*+------------+| month_name |+------------+| May        |+------------+*/

mysql.PERIOD_ADD

mysql.PERIOD_ADD(period,months_to_add)

Description

Adds a specified number of months to a period (formatted asYYYYMM orYYMM).

This function supports the following arguments:

  • period: The period, formatted as an integer (for example,202505).
  • months_to_add: The number of months to add.

Return data type

INT64

Example

The following example adds 3 months to the period202505:

SELECTmysql.PERIOD_ADD(202505,3)ASperiod_plus_3_months;/*+----------------------+| period_plus_3_months |+----------------------+| 202508               |+----------------------+*/

mysql.PERIOD_DIFF

mysql.PERIOD_DIFF(period1,period2)

Description

Returns the number of months between two periods (formatted asYYYYMM orYYMM).

This function supports the following arguments:

  • period1: The first period, formatted as an integer.
  • period2: The second period, formatted as an integer.

Return data type

INT64

Example

The following example returns the difference in months between two periods:

SELECTmysql.PERIOD_DIFF(202508,202505)asmonths_diff;/*+-------------+| months_diff |+-------------+| 3           |+-------------+*/

mysql.QUARTER

mysql.QUARTER(timestamp_expression)

Description

Returns the quarter of the year for aTIMESTAMP value, from 1 to 4.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

INT64

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values.

Limitations

If you provide an invalid timestamp, this function returns an error. In contrast,MySQL returnsNULL.

Example

The following example returns the quarter of the year from aTIMESTAMP value:

SELECTmysql.QUARTER(TIMESTAMP'2025-05-30')asquarter_of_year;/*+-----------------+| quarter_of_year |+-----------------+| 2               |+-----------------+*/

mysql.SECOND

mysql.SECOND(timestamp_expression)

Description

Returns the second from aTIMESTAMP value, from 0 to 59.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

INT64

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values.

Limitations

If you provide an invalid timestamp, this function returns an error. In contrast,MySQL returnsNULL.

Example

The following example returns the second from aTIMESTAMP value:

SELECTmysql.SECOND(TIMESTAMP'2025-05-30 14:30:45.123456')assecond;/*+--------+| second |+--------+| 45     |+--------+*/

mysql.STR_TO_DATE

mysql.STR_TO_DATE(string_expression,format_string)

Description

Converts a string into aTIMESTAMP value based on a specified format string.

This function supports the following argument:

  • string_expression: The date string.
  • format_string: ASTRING value that containsformat elementsto use withtimestamp_expression.

Return data type

TIMESTAMP

Differences from MySQL

This function supports a wider range of timestamps than the MySQL version.

Limitations

  • The following format specifiers are not supported:%c, %D, %f, %h, %i, %M, %r,%s, %u, %V, %W, %X, %x.
  • This function always returns aTIMESTAMP, even if the format string does notcontain time-related specifiers.

Example

The following example converts a string to aTIMESTAMP value:

SELECTmysql.STR_TO_DATE('May 30, 2025','%M %e, %Y')asdate_from_string;/*+------------------------+| date_from_string       |+------------------------+| 2025-05-30 00:00:00+00 |+------------------------+*/

mysql.SYSDATE

mysql.SYSDATE()

Description

Returns theTIMESTAMP at which the current query began to run.

This function doesn't support any arguments.

Return data type

TIMESTAMP

Differences from MySQL

This function is not an exact match for MySQL'sSYSDATE(). This function returnsthe start time of the entire query statement, so multiple calls within the samequery return the same value. In contrast, MySQL'sSYSDATE() returns the timeat which the function itself runs.

Example

The following example returns the current query's start timestamp:

SELECTmysql.SYSDATE()ASstart_time;/*+------------------------+| start_time             |+------------------------+| 2025-06-03 12:12:33+00 |+------------------------+*/

mysql.TIME

mysql.TIME(timestamp_expression)

Description

Extracts the time portion from aTIMESTAMP value and returns it as a string.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

STRING

Differences from MySQL

This function only acceptsTIMESTAMP values.

Example

The following example extracts the time from aTIMESTAMP value:

SELECTmysql.TIME(TIMESTAMP'2025-05-30 14:30:45.123')AStime_part;/*+-----------------+| time_part       |+-----------------+| 14:30:45.123000 |+-----------------+*/

mysql.TO_DAYS

mysql.TO_DAYS(date_expression)

Description

Converts aDATE value to the number of days since year zero. Year zero startsat 0000-00-00.

This function supports the following argument:

  • date_expression: The inputDATE value.

Return data type

INT64

Differences from MySQL

The epoch (day zero) is different from what MySQL uses.

Limitations

Use this function with caution for dates that precede 1970-01-01, as behavior mayvary from MySQL.

Example

The following example converts aDATE value to a number of days:

SELECTmysql.TO_DAYS(DATE'2025-05-30')asdays_since_year_0;/*+-------------------+| days_since_year_0 |+-------------------+| 739765            |+-------------------+*/

mysql.TO_SECONDS

mysql.TO_SECONDS(timestamp_expression)

Description

Converts aTIMESTAMP value to the number of seconds since0000-01-01 00:00:00.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP.

Return data type

INT64

Limitations

Use this function with caution on dates before the year 1901.

Example

The following example converts aTIMESTAMP to a number of seconds:

SELECTmysql.TO_SECONDS(TIMESTAMP'2025-05-30 00:00:00')ASseconds_since_day_0;/*+----------------------+| seconds_since_day_0  |+----------------------+| 63915807600          |+----------------------+*/

mysql.UNIX_TIMESTAMP

mysql.UNIX_TIMESTAMP(timestamp_expression)

Description

Returns the number of seconds from the Unix epoch (1970-01-01 00:00:00 UTC) to aspecifiedTIMESTAMP value.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

INT64

Differences from MySQL

Supports a wider range ofTIMESTAMP values than the MySQL version.

Limitations

The zero-argument version ofUNIX_TIMESTAMP() is not supported.

Example

The following example returns a Unix timestamp:

SELECTmysql.UNIX_TIMESTAMP(TIMESTAMP'2025-05-30 14:30:00')ASunix_ts;/*+------------+| unix_ts    |+------------+| 1748640600 |+------------+*/

mysql.UTC_DATE

mysql.UTC_DATE()

Description

Returns the current Coordinated Universal Time (UTC) date formatted as YYYY-MM-DD.

This function doesn't support any arguments.

Return data type

DATE

Example

The following example returns the current UTC date:

SELECTmysql.UTC_DATE()AScurrent_utc_date;/*+------------------+| current_utc_date |+------------------+| 2025-06-03       |+------------------+*/

mysql.WEEK

mysql.WEEK(timestamp_expression)

Description

Returns the week number for aTIMESTAMP value, from 1 to 53.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

INT64

Differences from MySQL

This function only acceptsTIMESTAMP values, while the MySQL version also acceptsDATE andDATETIME values. The MySQL version also has amode argument tocontrol the week's start day and range. This function does not support themodeargument and corresponds to MySQL's default mode (mode 0).

Limitations

If you provide an invalid timestamp, this function returns an error. In contrast,MySQL returnsNULL.

Example

The following example returns the week number from aTIMESTAMP value:

SELECTmysql.WEEK(TIMESTAMP'2025-05-30')asweek_num;/*+----------+| week_num |+----------+| 21       |+----------+*/

mysql.WEEKDAY

mysql.WEEKDAY(timestamp_expression)

Description

Returns the weekday index for aTIMESTAMP value. The index uses Monday as thefirst day of the week (Monday = 0, Sunday = 6).

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

INT64

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values. The underlying day-of-week logic differs, but thisfunction adjusts the result to match MySQL'sWEEKDAY() output.

Example

The following example returns the weekday index for a given timestamp:

SELECTmysql.WEEKDAY(TIMESTAMP'2025-05-30')asweekday_index;/*+---------------+| weekday_index |+---------------+| 4             |+---------------+*/

mysql.WEEKOFYEAR

mysql.WEEKOFYEAR(timestamp_expression)

Description

Returns the calendar week of the year for aTIMESTAMP value, from 1 to 53. Thisfunction uses the ISO 8601 standard for week numbering.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

INT64

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values.

Limitations

If you provide an invalid timestamp, this function returns an error. In contrast,MySQL returnsNULL.

Example

The following example returns the week of the year from aTIMESTAMP value:

SELECTmysql.WEEKOFYEAR(TIMESTAMP'2025-05-30')asweekofyear_iso;/*+----------------+| weekofyear_iso |+----------------+| 22             |+----------------+*/

mysql.YEAR

mysql.YEAR(timestamp_expression)

Description

Returns the year from aTIMESTAMP value.

This function supports the following argument:

  • timestamp_expression: The inputTIMESTAMP value.

Return data type

INT64

Differences from MySQL

This function only acceptsTIMESTAMP values. The MySQL version also acceptsDATEandDATETIME values.

Limitations

If you provide an invalid timestamp, this function returns an error. In contrast,MySQL returnsNULL.

Example

The following example returns the year from aTIMESTAMP value:

SELECTmysql.YEAR(TIMESTAMP'2025-05-30')asyear_value;/*+------------+| year_value |+------------+| 2025       |+------------+*/

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.