How to do "select current_timestamp" in hsqldb?
In a select I use
SELECT CURRENT_DATE AS today, CURRENT_TIME AS now FROM (VALUES(0))
@alexdown's answer is quite right -- under 1.8 you need a one-row relation to do this, like Oracle's DUAL
or the InterBase/Firebird RDB$DATABASE
table.
When you move to the 2.0 series, however, you'll be able to use the SQL-99 "VALUES constructor" without reliance on a one-row relation:
sql> VALUES (current_timestamp);2010-04-22 15:22:40.997
If you need to rename the column from the vendor-specific defaults that VALUES picks, you can always employ a select: SELECT * FROM (VALUES (current_timestamp)) v(my_new_name)
You can use
CALL current_timestamp
to retrieve the current timestamp. According to a discussion on the HSQL mailing list this is much more efficient than doing a dummy select from INFORMATION_SCHEMA.SYSTEM_TABLES
.