CODEWITHSUNDEEP
mysqldatetime
Wrapper
Wrapper

Reputation: 932

Calculate the difference in hours between two String dates in MySQL?

I have two String columns in MySQL database. Those two columns were populated from a Java program in following way:

System.currentTimeMillis(); //first column
System.currentTimeMillis(); + someStringHours //second column; the String, someStringDays reprensents some number of days, let's say 5 hours in millis...

Which function in MySQL can be used to calculated the difference to get number of hours between these two columns?

Upvotes: 1

Views: 635

Answers (2)

O. Jones
O. Jones

Reputation: 108776

You call them string dates but they are actually UNIX timestamps in milliseconds (also called Javascript timestamps). That's what System.currentTimeMillis() generates. It's a Java long data item, and a MySQL BIGINT data item. You can store it in a string. (You can store it that way if you must, but searching and sorting numbers stored as strings is an unreliable mess; beware!)

A typical Javascript timestamp (or UNIX timestamp in milliseconds) is a big integer like 1600858176374456. 1 600 858 176 374 456.

You can convert such a timestamp to a MySQL TIMESTAMP value with FROM_UNIXTIME() like this

    FROM_UNIXTIME(column * 0.001)

Why multiply the column value by 0.001 (that is, divide it by 1000)? Because FROM_UNIXTIME() takes the timestamp in seconds, whereas System.currentTmeMillis() generates it in milliseconds.

Then you can use DATEDIFF() like this

    DATEDIFF(FROM_UNIXTIME(laterTs*0.001),FROM_UNIXTIME(earlierTs*0.001))

This gives an integer number of days.

If you need the time difference in some other unit, such as hours, minutes, or calendar quarters, you can use TIMESTAMPDIFF(). This gives you your difference in hours.

TIMESTAMPDIFF(HOUR,
              FROM_UNIXTIME(laterTs*0.001),
              FROM_UNIXTIME(earlierTs*0.001));

You can use SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR as the time unit in this function. Pro tip: Use your DBMS's date arithmetic functions if you possibly can. They've worked out all sorts of edge cases for you already.

And, by the way, if you declare your columns like this (Timestamp with a millisecond precision: How to save them in MySQL):

laterTs    TIMESTAMP(3),
earlierTs  TIMESTAMP(3),

You'll have an easier time indexing on and searching by these times.

Upvotes: 1

kelvin
kelvin

Reputation: 1538

SELECT (1600858176374-1600944576374)/(24*60*60*1000) as Days

Where (1600858176374-1600944576374) are timestamps and (246060*1000) is a mills in day

Upvotes: 0

Related Questions