Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

In postgres, timestamp with time zone can be abbreviated as timestamptz, and timestamp without time zone as timestamp. I will use the shorter type names for simplicity.

Getting the Unix timestamp from a postgres timestamptz like now() is simple, as you say, just:

select extract(epoch from now());

That's really all you need to know about getting the absolute time from anything of type timestamptz, including now().

Things only get complicated when you have a timestamp field.

When you put timestamptz data like now() into that field, it will first be converted to a particular timezone (either explicitly with at time zone or by converting to the session timezone) and the timezone information is discarded. It no longer refers to an absolute time. This is why you don't usually want to store timestamps as timestamp and would normally use timestamptz — maybe a film gets released at 6pm on a particular date in every timezone, that's the kind of use case.

If you only ever work in a single time zone you might get away with (mis)using timestamp. Conversion back to timestamptz is clever enough to cope with DST, and the timestamps are assumed, for conversion purposes, to be in the current time zone. Here's an example for GMT/BST:

select '2011-03-27 00:59:00.0+00'::timestamptz::timestamp::timestamptz
     , '2011-03-27 01:00:00.0+00'::timestamptz::timestamp::timestamptz;

/*
|timestamptz           |timestamptz           |
|:---------------------|:---------------------|
|2011-03-27 00:59:00+00|2011-03-27 02:00:00+01|
*/

_DBFiddle

But, note the following confusing behaviour:

set timezone to 0;

values(1, '1970-01-01 00:00:00+00'::timestamp::timestamptz)
    , (2, '1970-01-01 00:00:00+02'::timestamp::timestamptz);

/*
|column1|column2               |
|------:|:---------------------|
|      1|1970-01-01 00:00:00+00|
|      2|1970-01-01 00:00:00+00|
*/

_DBFiddle

This is because:

PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both […] as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type…In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication

The answer lies in fully understanding the TIMEDIFF function and what it returns as an integer.

First, let's consider TIMEDIFF in its native element and what it returns:

mysql> select timediff('0:0:0','1:0:0') as timediff;
+-----------+
| timediff  |
+-----------+
| -01:00:00 | 
+-----------+
1 row in set (0.00 sec)

Second, what does TIMEDIFF return in an integer context - done by adding zero:

mysql> select timediff('0:0:0','1:0:0')+0 as timediff2;
+--------------+
| timediff2    |
+--------------+
| 10000.000000 | 
+--------------+
1 row in set (0.01 sec)

Now, we use the TIME_TO_SEC function to return what we actually want:

mysql> select time_to_sec(timediff('0:0:0','1:0:0')+0)/60 as timediff3;
+-----------+
| timediff3 |
+-----------+
|   60.0000 | 
+-----------+
1 row in set (0.00 sec)

The integer value returned by TIMEDIFF is not minutes or seconds, but rather a base-10 representation of the actual time - so 01:00:00 becomes 10000, and 05:05:01 becomes 50501.

Instead of using TIME_TO_SEC one can also use TIMESTAMPDIFF instead of TIMEDIFF and set the units to whatever is desired:

mysql> select timestampdiff(MINUTE,'2012-04-13 0:0:0','2012-04-13 1:0:0') as timediff4;
+-----------+
| timediff4 |
+-----------+
|        60 | 
+-----------+
1 row in set (0.00 sec)

Instead of MINUTE, you can also use FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

The time functions in MySQL (in this case, version 5.1) are all described in the manual. The online manual also links to other versions (such as MySQL 5.5) as well.