CODEWITHSUNDEEP
sqldatabasehsqldbembedded-database
cherouvim
cherouvim

Reputation: 31903

How to do "select current_timestamp" in hsqldb?

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

Upvotes: 20

Views: 33442

Answers (5)

fredt
fredt

Reputation: 24372

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)

Upvotes: 4

Thilo-Alexander Ginkel
Thilo-Alexander Ginkel

Reputation: 6958

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.

Upvotes: 5

jeanlou1370
jeanlou1370

Reputation: 382

In a select I use 

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

Upvotes: 28

pilcrow
pilcrow

Reputation: 58619

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

Upvotes: 11

alexdown
alexdown

Reputation: 262

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.

Upvotes: 4

Related Questions