ANSI standard of UNIX_TIMESTAMP()

Question

UNIX_TIMESTAMP() isn't an ANSI standard keyword but an addition to the MySQL syntax. However, since I'm supporting multiple DB's, is there an ANSI standard way to write UNIX_TIMESTAMP();

Thanks

Answer 1

As far as I know, no.

Every database handles this differently.

For example, in Oracle, you have to manually generate the timestamp with something like:

SELECT (sysdateColumn - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) AS alias FROM tableName;

In MSSSQL:

SELECT DATEADD(s, yourDateColumn, '19700101') AS alias FROM tableName

In PGSQL:

SELECT date_part('epoch', timestampColumn) AS alias FROM tableName

Edit: as AlexKuznetsov pointed out, there are two totally different usages of MySQL's UNIX_TIMESTAMP() function. I assumed the latter, UNIX_TIMESTAMP(date) (for native format to epoch conversion) for the above answer.

Answer 2

You failed to define UNIX_TIMESTAMP If the following is true: UNIX_TIMESTAMP(date) returns the value of the argument as seconds since '1970-01-01 00:00:00'

then use DATEDIFF function