Mysql Unix_timestamp function

Question

In oracle converting this date 2012-07-03 11:38:41 to unix_timestamp we get

    select (to_date('2012-07-03 11:38:41','YYYY-MM-DD HH24:MI:SS') -
    to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))*86400 as unix_timestamp
    from dual
SQL> /

UNIX_TIMESTAMP
--------------
    1341315521

But when i try the same on mysql server

select UNIX_TIMESTAMP('2012-07-03 11:38:41')
1341311921

Server Settings are something like this

**mysql**> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2012-07-26 15:27:31 |
+---------------------+
1 row in set (0.00 sec)

**Unix** >Thu Jul 26 15:27:56 BST 2012

**oracle**>select current_timestamp from dual;

CURRENT_TIMESTAMP
------------------------------------
26-JUL-12 15.27.16.967258 +01:00

How do i make sure oracle and mysql give me the same values ?

Pekka · Accepted Answer · 2012-07-26 11:58:28Z

1

The difference between the two values you show is 3600 seconds, i.e. 1 hour.

Most likely, the two servers' timezone settings vary by one hour.

See https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html for time zone info in the mySQL server. Here is some in-depth info for Oracle's handling of time zones.

answered Jul 26, 2012 at 11:58

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jhon.smith Over a year ago

Apologies i forgot to mention that both the servers are hosted on the same Unix box Good observation though

Pekka Over a year ago

@jhon then either mySQL or Oracle is set to the wrong time zone. Which one returns the correct time stamp for where the server is located?

jhon.smith Over a year ago

i have updated the question with current timestamp settings for Mysql Oracle and Unix and they dont seem to be off by 3600Seconds interesting

jhon.smith Over a year ago

Pekka thanks for noticing that i was off by 3600 seconds and this important finding by you actually triggered my answer below so I am marking your answer as correct

jhon.smith · Accepted Answer · 2012-07-31 04:12:13Z

1

I used this trick unix_timestamp(cast(sys_extract_utc(systimestamp) as date

And then i also wrote a function called unix_timestamp

create or replace
function unix_timestamp(in_date date)
return number DETERMINISTIC PARALLEL_ENABLE AS
l_date date;
begin
return trunc((in_date -to_date ( '01-jan-1970', 'dd-mon-yyyy' ))*86400);
end;
/

answered Jul 31, 2012 at 4:12

jhon.smith

2,0438 gold badges35 silver badges60 bronze badges

Collectives™ on Stack Overflow

Mysql Unix_timestamp function

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related