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
views:
1180answers:
2
+1
A:
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
2009-05-26 09:10:13
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
2009-05-26 10:04:34
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
2009-05-27 07:13:52
A:
@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)
pilcrow
2010-04-22 15:34:22