This document introduces the syntax of the date functions in Spark SQL.
This document provides both New Calculation Column formulas and Spark SQL syntax, allowing you to choose freely.
You are advised to use DATETONUMBER in New Calculation Column of FineDataLink to convert the date into a 13-digit timestamp.
You can also use the syntax in Spark SQL.
Syntax
Example
unix_timestamp(): Returns the timestamp in the local time zone.
select unix_timestamp()
from_unixtime(Bigint-type unix timestamp, Format): Converts the number of seconds from unix epoch to a string representing the timestamp of that moment in the given format. For example, from_unixtime(1250111000,"yyyy-MM-dd") returns 2009-03-12.
select from_unixtime(1557737000,"yyyy-MM-dd hh:mm:ss")
unix_timestamp(Date): Converts the time string with the yyyy-MM-dd HH:mm:ss pattern to a timestamp. For example, unix_timestamp('2009-03-20 11:30:01') returns 1237573801.
select unix_timestamp('2019-05-13 04:43:20')
unix_timestamp(Date, Pattern): Converts a date string with a specified pattern to a Unix timestamp. Returns 0 if the format is incorrect. For example, unix_timestamp('2009-03-20', 'yyyy-MM-dd') returns 1237532400
select unix_timestamp('2019/05/13 04:43:20',"yyyy/MM/dd hh:mm:ss"
Description
Spark SQL Syntax
Formula in New Calculation Column
Recommendation
Returns the year, month, and day parts of a datetime string.
to_date(Timestamp)
For example, to_date("1970-01-01 00:00:00") returns 1970-01-01.
You can use the following formula in New Calculation Column.
TODATE(LEFT(2023-06-13 00:01:27))
The time field must be in the text format.
The time field must be in the date or timestamp format.
Returned value: 2023-06-13 00:00:00
You are advised to use the TODATE, LEFT, and FORMAT functions in New Calculation Column of FineDataLink.
Returns the year part of a datetime string.
year(Date)
For example, year("1970-01-01") returns 1970.
YEAR("2000/1/1")
You are advised to use the YEAR function in New Calculation Column of FineDataLink.
Returns the quarter of the given datetime as an integer.
quarter(Date/Timestamp)
For example, quarter('2015-04-08 ') returns 2.
Not supported currently.
Returns the month of the date.
month(Date)
For example, month("1970-11-01") returns 1.
MONTH("2000/1/1")
You are advised to use the MONTH function in New Calculation Column of FineDataLink.
Returns the day of the date.
day(Date)
For example, day("1970-11-01") returns 1.
DAY("2000/1/1")
You are advised to use the DAYfunction in New Calculation Column of FineDataLink.
Returns the hour of the specified time.
hour(Date)
For example, hour('2009-07-30 12:58: 59') returns 12.
HOUR("11:32:40")
You are advised to use the HOUR function in New Calculation Column of FineDataLink.
Returns the minute of the specified time.
minute(Date)
For example, minute('2009-07-30 12:58:59') returns 58.
MINUTE("15:36:25")
You are advised to use the MINUTE function in New Calculation Column of FineDataLink.
Returns the second of the specified time.
second(Date)
For example, second('2009-07-30 12:58:59') returns 59.
SECOND("15:36:25")
You are advised to use the SECOND function in New Calculation Column of FineDataLink.
Returns the week number of a given date within a year.
weekofyear(Date)
For example, weekofyear ("1970 - 11 - 01") returns 44.
WEEK("2010/1/1")
You are advised to use the WEEK function in New Calculation Column of FineDataLink.
Returns the number of days, months, or years between two specified dates.
datediff(End date, Start date)
For example, datediff('2009-03-0 1', '2009-02-27') returns 2.
DATEDIF("2001/2/28","2004/3/20","Y")
You are advised to use the DATEDIF function in New Calculation Column of FineDataLink.
Returns the date that is years/months/days/hours/minutes/seconds after the provided date.
Returns the date that is days after the start date: date_add(Start date, Integer number of days).
Returns the date that is days before the start date: date_sub (Start date, Integer number of days).
ADDTODATE("2023-01-01", "y", 1)
You are advised to use the ADDTODATE function in New Calculation Column of FineDataLink.
Returns the current time.
current_timestamp
Example: select current_timestamp
NOW()
You are advised to use the NOW function in New Calculation Column of FineDataLink.
Returns the current date.
current_date
Example: select current_date
TODATE(FORMAT(NOW(),"yyyy-MM-dd"))
You are advised to use the TODATE(FORMAT(NOW(),"yyyy-MM-dd")) formula in New Calculation Column of FineDataLink.
add_months(Start date, Integer number of months): Returns the date the specified number of months after the provided date.
Example:
select add_months('2019-05-13',120)
You are advised to use the following formula in New Calculation Column of FineDataLink: date(year(today()),MONTH(today())+num_months,day(today())). For details, see Common Date Formulas.
last_day(Date): Returns the last day of the month to which the given date belongs without the hour, minute, and second parts (HH:mm:ss).
select last_day('2019-02-01')
You are advised to use the following formula in New Calculation Column of FineDataLink: DATE(YEAR(Date), MONTH(Date) + 1, 1-1). For details, see Common Date Formulas.
Use the TRUNC function of Spark SQL to get the start of the year or month for the time. The syntax is trunc(Date, Format).
select trunc("2016-06-26","MM"),trunc("2016-06-26","YY")
The start date of the year and the start date of the month for the date 2016-06-26 are returned, as shown in the following figure.
You can also use the following formula in New Calculation Column of FineDataLink: DATE(YEAR(NOW()),01,01) and DATE(YEAR(NOW()),MONTH(NOW()),01).
For more examples, see Common Date Formulas.
months_between (Date1, Date2): Returns the number of months between the first date and the second date. If the Date1 value is later than the Date2 value, the result is a positive number. If the Date1 value is earlier than the Date2 value, the result is a negative number. Otherwise, returns 0.0.
select months_between("2019-04-01", "2019-06-01")
date_format(Date/Timestamp/String, Format): Converts a date/timestamp/string to a value of string in the specified date format.
select date_format('2019-05-13','MM/dd')
You are advised to use the FORMAT function in New Calculation Column of FineDataLink to return date data in the specified format.
next_day(Start date, Day of week): Returns the date of the first specified day of week (DOW) after the provided date.
For example, select next_day('2019-05-13', 'TU') returns the date of the first Tuesday after May 13, 2019, as shown in the following figure.
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy