18

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

cherouvim
  • 31,725
  • 15
  • 104
  • 153

5 Answers5

27

In a select I use 

 SELECT CURRENT_DATE AS today, CURRENT_TIME AS now FROM (VALUES(0))
jeanlou1370
  • 372
  • 3
  • 6
  • 1
    I prefer this solution as it doesn't require me to have any tables (for a plain connection test). – Peter Walser Apr 25 '16 at 17:27
  • 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. – Brian Jul 24 '20 at 19:41
10

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

Community
  • 1
  • 1
pilcrow
  • 56,591
  • 13
  • 94
  • 135
5

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.

Thilo-Alexander Ginkel
  • 6,898
  • 10
  • 45
  • 58
4

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)
fredt
  • 24,044
  • 3
  • 40
  • 61
4

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.

alexdown
  • 262
  • 3
  • 9
  • 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] – cherouvim May 26 '09 at 10:04
  • 2
    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.. – alexdown May 27 '09 at 07:13
  • 3
    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` – Adam Feb 12 '11 at 22:34
  • 1
    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` – Harald Brabenetz Jul 02 '13 at 12:59