How to do "select current_timestamp" in hsqldb?

Question

Oracle:

select systimestamp from dual

MySQL:

select current_timestamp

SQL Server:

select current_timestamp

PostgreSQL:

select current_timestamp

The question is, how can I get the current timestamp in HSQLDB? I use version 1.8.0.10

Goldfarb

Answer 1

You can write

select current_timestamp from tablename  

where tablename is a real table in your database.

The result of the query is only the current timestamp.

Thayer

Thanks, but with hibernate I do session.createSQLQuery("select current_timestamp from users") and I get empty list for .list() and null for .uniqueResult(). The table exists. If I put a non existent table I correctly get java.sql.SQLException: Table not found in statement [select current_timestamp from qwerty] – Goldfarb

is the table you are using empty? for some reason I did a couple of tests and seems that if the result of the select is an empty list, you don't have the timestamp, if the result of the select is a list of rows, you get as many timestamps as the number of rows.. – Thayer

I know this is an old question, but if you want to write generic code (e.g. in a reusable library), that does not depend on specific a specific database, you could try using something like: SELECT TOP 1 current_timestamp FROM INFORMATION_SCHEMA.SYSTEM_TABLES – Custodial

If the Table could be empty you need to wrap the timestamp with max aggregation and coalesce: select coalesce(max(current_timestamp()), current_timestamp()) from tablename – Philpott

Answer 2

In a select I use

 SELECT CURRENT_DATE AS today, CURRENT_TIME AS now FROM (VALUES(0))

Blindage

I prefer this solution as it doesn't require me to have any tables (for a plain connection test). – Vanny

I found this answer more useful. My goal was to just do a simple select statement to verify the syntax. Most SQL implementation allow that without a FROM clause. This was a great work around. – Worden

Answer 3

@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)

Hizar

Answer 4

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.

Preraphaelite

Answer 5

With HSQLDB 2.1 and later you have all the options.

With the connection property hsqldb.syntax_ora, hsqldb.syntax_pgs, hsqldb.syntax_mss or hsqldb.syntax_mys=true you can use the forms supported by other databases. The equivalent SQL is SET DATABASE SQL SYNTAX ORA TRUE, and similar for other dialects.

The native, SQLStandard form, supported by HSQLDB in all modes is this:

VALUES (CURRENT_TIMESTAMP)

Lodhia

It's actually SET DATABASE SQL SYNTAX ORA TRUE. You were missing the "SQL" @fredt. – Aforethought