3

My friend asking me if there a way to use 1 query to select datetime and use it for any database.

Atm he using this query for Oracle :

SELECT vt_sysdate() FROM dual;

I know dual table is for Oracle only. But he want to use this same query for other database, for example PostgreSQL, as well.

So is there a way to make this query run on every database (Maybe by create a dual table for every database). Or is there a query to get system datetime that work on any database ?

Thank you for you help.

mameo
  • 639
  • 8
  • 25
  • Are you looking for all databases or just Oracle and Postgres? – Gordon Linoff Mar 26 '14 at 02:18
  • 1
    The SQL standard provides the `current_timestamp` and `current_date` pseudo functions, but I don't think there's any completely portable way to do a scalar query. PostgreSQL allows you to write simply `SELECT current_date;` but I don't think Oracle understands that. You could also use `SELECT current_date FROM (VALUES (1)) x;` but again, I don't think Oracle lets you use `VALUES` like that. (A cynic could note that Oracle doesn't feel any desire to be compatible, as they quite like people to depend on Oracle...) – Craig Ringer Mar 26 '14 at 02:18
  • @Gordon Linoff : my friend ask for general, not specific database. Just that we use mostly Oracle and Postgresql so i mention them here. – mameo Mar 26 '14 at 02:21

3 Answers3

3

No, I don't think so, because Oracle insists on being weird, and MS SQL doesn't seem to support the standard current_date at all.

MySQL accepts SELECT current_date;, doesn't understand VALUES.

PostgreSQL accepts SELECT current_date; or VALUES.

MS SQL doesn't seem to understand current_date at all.

Oracle understands current_date but not scalar queries without FROM.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
3

From wikipedia:

  • Firebird has a one-row system table RDB$DATABASE that is used in the same way as Oracle's DUAL, although it also has a meaning of its own.
  • IBM DB2 has a view that resolves DUAL when using Oracle Compatibility 1
  • Microsoft Access: A table named DUAL may be created and the single-row constraint enforced via ADO 2
  • MySQL allows DUAL to be specified as a table in queries that do not need data from any tables.3
  • PostgreSQL: A DUAL-view can be added to ease porting from Oracle.4
  • SQLite: A VIEW named "dual" that works the same as the Oracle "dual" table can be created as follows: "CREATE VIEW dual AS SELECT 'x' AS dummy;"
  • SAP HANA has a table called DUMMY that works the same as the Oracle "dual" table.

You might try current_timestamp instead of current_date, as this appears to be the best-supported option. As an aside, writing vendor-neutral SQL seems to be basically impossible without some kind of translation layer.

Community
  • 1
  • 1
dwurf
  • 12,393
  • 6
  • 30
  • 42
1

Thank everyone for your support.

After looking at everyone answer, after a while discuss with my friend, we came to a conclusion that it won't be possible to use one query for all database. So we create a function, check database, use correct function to get time and return it.

We will have to create a dummy table dual on any database we use like in this blog suggest. A dummy table with 1 column and 1 record.

Thank you all.

mameo
  • 639
  • 8
  • 25
  • You solution to create one dummy table makes all other databases match Oracle, who is actually the odd one out. – Nick.Mc Mar 26 '14 at 03:47
  • We know they are the only one out there using dual table. But my friend project start with Oracle first so they don't want to change their query which already have dual table in there – mameo Mar 28 '14 at 04:55