http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> what is equivalent syntax in starro...
# questions-and-troubleshooting
c
@Rocky what is equivalent syntax in starrocks for this? 
*COALESCE*(table.datetime field, *TIMESTAMP* '9999-12-31 23:59:59')
im having this error using that: SQL Error [1064] [42000]: Getting syntax error at line 4, column 55. Detail message: Unexpected input ''9999-12-31 235959'', the most similar input is {',', ')'}.
r
Hi! Rocky here. I can help you with that syntax error. In StarRocks, the standard SQL literal syntax 
TIMESTAMP 'YYYY-MM-DD HH:MM:SS'
is not supported in the same way it is in some other databases. The parser expects 
TIMESTAMP
to be used as a function 
TIMESTAMP(expr)
or as a column name, which is why you're getting a syntax error when you follow it immediately with a string. You can achieve the same result using any of the following equivalent syntaxes: 1. Simplest approach (Implicit conversion) If your 
table.datetime_field
is already a 
DATETIME
type, StarRocks will automatically cast the string literal for you:
Copy code
sql
COALESCE(table.datetime_field, '9999-12-31 23:59:59')
2. Using explicit CAST This is the most robust way to ensure the type matches exactly:
Copy code
sql
COALESCE(table.datetime_field, CAST('9999-12-31 23:59:59' AS DATETIME))
3. Using the TIMESTAMP() function If you want to keep the 
TIMESTAMP
keyword, you must use it as a function with parentheses:
Copy code
sql
COALESCE(table.datetime_field, TIMESTAMP('9999-12-31 23:59:59'))
Note: StarRocks primarily uses the 
DATETIME
type for this range (up to '9999-12-31 235959'). If your field is specifically the 
TIMESTAMP
type (which in StarRocks is often used for Unix epoch ranges), keep in mind that the standard 
TIMESTAMP
range in many systems only goes up to '2038-01-19'. However, for your literal '9999-12-31', 
DATETIME
is the appropriate type to use. References • sql-reference/sql-functions/date-time-functions/to_datetime.md • sql-reference/sql-functions/date-time-functions/timestamp.md • sql-reference/sql-functions/date-time-functions/to_tera_timestamp.md • sql-reference/sql-functions/date-time-functions/to_datetime_ntz.md • sql-reference/sql-functions/date-time-functions/from_unixtime.md • sql-reference/sql-functions/date-time-functions/unix_timestamp.md • administration/management/timezone.md
c
@Rocky how about this? *COALESCE*(table.datetime_field, current_timestamp AT TIME ZONE 'UTC')
r
The syntax you provided, 
COALESCE(table.datetime_field, current_timestamp AT TIME ZONE 'UTC')
, is common in some other database systems (like PostgreSQL), but StarRocks does not support the 
AT TIME ZONE
operator. In StarRocks, you can achieve this more simply using the built-in UTC functions or the 
CONVERT_TZ
function. Recommended Syntax in StarRocks If your goal is to fall back to the current UTC time when the field is NULL, use `UTC_TIMESTAMP()`:
Copy code
sql
SELECT COALESCE(table.datetime_field, UTC_TIMESTAMP()) 
FROM table;
Alternative: Using CONVERT_TZ If you specifically need to convert a timestamp from one timezone to another, you use the 
CONVERT_TZ
function:
Copy code
sql
-- Syntax: CONVERT_TZ(dt, from_tz, to_tz)
SELECT COALESCE(
    table.datetime_field, 
    CONVERT_TZ(CURRENT_TIMESTAMP(), 'Asia/Shanghai', 'UTC')
) 
FROM table;
Summary of compatible functions: * 
COALESCE(expr1, expr2, ...)
: Supported. Returns the first non-null value. * 
CURRENT_TIMESTAMP()
: Supported. Returns the current time in the session's timezone. * 
UTC_TIMESTAMP()
: Supported. Returns the current time in UTC. * 
CONVERT_TZ(dt, from, to)
: Supported. Used for all timezone conversions. Note: Ensure that the data types in 
COALESCE
match (e.g., both are 
DATETIME
). 
UTC_TIMESTAMP()
returns a 
DATETIME
value, which is compatible with most datetime fields. References • sql-reference/sql-functions/date-time-functions/utc_timestamp.md • sql-reference/sql-functions/date-time-functions/utc_time.md • sql-reference/sql-functions/date-time-functions/unix_timestamp.md • sql-reference/sql-functions/date-time-functions/to_datetime.md • administration/management/timezone.md • sql-reference/sql-functions/date-time-functions/to_datetime_ntz.md • sql-reference/sql-functions/date-time-functions/from_unixtime.md
c
@Rocky can you also help me align these in starrocks syntax? *DATE_DIFF*('year', *CAST*(users.dob AS DATE), CURRENT_DATE)
r
In StarRocks (version 3.1 and later), you can use the 
date_diff
function which is very similar to your snippet, or the specialized 
years_diff
function. Option 1: Using 
date_diff
(Recommended for v3.1+) The syntax for 
date_diff
in StarRocks is 
date_diff(unit, expr1, expr2)
, where it calculates expr1 - expr2. To get the age (Current Date minus Date of Birth), use:
Copy code
sql
SELECT DATE_DIFF('year', CURRENT_DATE(), CAST(users.dob AS DATE))
Option 2: Using 
years_diff
 StarRocks also provides a shorthand function specifically for year differences:
Copy code
sql
SELECT YEARS_DIFF(CURRENT_DATE(), CAST(users.dob AS DATE))
Key Differences to Note: 1. Function Name: StarRocks supports both 
date_diff
(with unit) and 
datediff
(which only returns days). 2. Order of Arguments: In StarRocks 
date_diff
, the calculation is 
expr1 - expr2
. So, put the more recent date (
CURRENT_DATE()
) first to get a positive age. 3. Current Date: You can use 
CURRENT_DATE()
or 
curdate()
. 4. Unit Argument: For 
date_diff
, the unit 
'year'
must be a string literal (wrapped in single quotes). Note for older versions (< 3.1): If you are on an older version of StarRocks that doesn't support the 
unit
parameter in 
date_diff
, you should use `TIMESTAMPDIFF`:
Copy code
sql
SELECT TIMESTAMPDIFF(YEAR, CAST(users.dob AS DATE), CURRENT_DATE())
_(Note: 
TIMESTAMPDIFF
calculates 
expr2 - expr1
, so the order is reversed compared to 
date_diff
.)_ References • sql-reference/sql-functions/date-time-functions/date_diff.md • sql-reference/sql-functions/date-time-functions/timestampdiff.md • sql-reference/sql-functions/date-time-functions/datediff.md • sql-reference/sql-functions/date-time-functions/days_diff.md • sql-reference/sql-functions/date-time-functions/years_diff.md • sql-reference/sql-functions/date-time-functions/weeks_diff.md • sql-reference/sql-functions/date-time-functions/hours_diff.md • sql-reference/sql-functions/date-time-functions/date_sub.md
Open in Slack
PreviousNext
Powered by