CODEWITHSUNDEEP
sqltimezoneepoch
feodor
feodor

Reputation: 1

Epoch time shows different date in different time zones?

I have an epoch time(seconds) . It shows me different times in JAVA, when i do

new Date(time*1000)

and in SQL when i do

SELECT cast((DATE '1970-01-01' + time/24/60/60) as timestamp) from dual.

The problem mainly is, i need data of a particular interval between 2 AM and 4 AM. So when i pass the seconds value to the SQL query. it interprets it as between 9AM and 11 AM. I understand that this is because of the time zone difference.

How can i overcome this problem ?

Upvotes: 0

Views: 390

Answers (2)

John Meyer
John Meyer

Reputation: 463

My suggestion for dealing with any time zone related issues is to always handle times internally as UTC and store them in the data store as UTC. Only convert them to a local time only for display to the user.

Upvotes: 0

mellamokb
mellamokb

Reputation: 56779

Try using a timestamp instead:

SELECT cast((TIMESTAMP '1970-01-01 00:00:00.00' + time/24/60/60) as timestamp)
  FROM dual

Demo: http://www.sqlfiddle.com/#!4/d41d8/1465

Upvotes: 1

Related Questions