2

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.

Improve this question
5
  • Do you want to store date in MySQL right ? -> stackoverflow.com/questions/2400955/… Commented Aug 25, 2012 at 15:43
  • 1
    This seems like a timezone issue. You can use the MySQL TIME type, too. Commented Aug 25, 2012 at 15:43
  • For the java.sql.Time-class, there also is a constructor like this: public Time(int hour, int minute, int second). Commented Aug 25, 2012 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. Commented Aug 25, 2012 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. Commented Aug 25, 2012 at 15:49

2 Answers 2

Reset to default
1

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.

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

Comments

0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.