Set Time Without Knowing Date

Question

I want to be able to store time values in a mysql database without actually knowing a specific date they are associated with. I am using the java.sql.time to store the data.

long timeInMillis = 43200000; //This is 12 hours in milliseconds.    
Time time = new Time(timeInMillis);

For whatever reason this is giving me a time of 07:00:00 and it should be 12:00:00. I'm assuming this is because when setting a time variable it is based on the amount of time past a specific date. How do I set a time variable without actually using a pre-defined date?

The reason I am doing this is because I want to store a week day and a time range in a database. So Sunday between 12 and 1 would be 0 between 12:00:00 and 13:00:00. I want to be able to compare an actual date value against the database and see if the date falls between the time periods based on the dates day of week regardless of the month or year. Storing full dates in the database for each possible weekday and time would result in thousands of unnecessary entries.

Do you want to store date in MySQL right ? -> http://stackoverflow.com/questions/2400955/how-to-store-java-date-to-mysql-datetime — Anuj Kulkarni, Aug 25 '12 at 15:43
This seems like a timezone issue. You can use the MySQL TIME type, too. — Kevin Mangold, Aug 25 '12 at 15:43
For the java.sql.Time-class, there also is a constructor like this: `public Time(int hour, int minute, int second)`. — influjensbahr, Aug 25 '12 at 15:44
That constructor is deprecated...unfortunately. The only proper way to set a time variable is by using the Time(long millis) constructor. — ryandlf, Aug 25 '12 at 15:45
@Kevin Mangold Hmm..I never thought about that. How do I adjust for timezone? The time variable I am using is mysql. — ryandlf, Aug 25 '12 at 15:49

score 1 · Answer 1 · answered Aug 25 '12 at 18:44

For whatever reason this is giving me a time of 07:00:00 and it should be 12:00:00. I'm assuming this is because when setting a time variable it is based on the amount of time past a specific date

Can happen due to TimeZone difference. Check for timezone information while saving and retrieving values.

score 0 · Accepted Answer · edited May 23 '17 at 12:27

This is likely a timezone issue, but I solved the problem by passing the time values into the database as a string. It is probably best to use a physical string in this instance anyways unless timezone properties are important.

String startTime = "12:00:00";
String endTime = 01:00:00;

If timezone is important I assume the best method to use unless you are preparsing your date object would be to set the timezone directly in your mysql statement for the current session. More details can be found here: How do I set the time zone of MySQL?

EDIT: Turns out in the end this wasn't actually a time zone issue (although in other circumstances it could be, so don't discredit adjusting for time zone). I was calculating the time in milliseconds incorrectly.

Set Time Without Knowing Date

2 Answers2