I store a number of rows in DB with the timestamp of that moment in milliseconds. Now if I need to retrieve all rows of a given day, like today, how do I correctly create the starting and ending milliseconds of that day?
I know about SimpleDateFormat and Calendar.getInstance() briefly, but would I need to do string manipulation (which I want to avoid) to get todays date only, add the hours part and then convert it back into milliseconds, or is there a better way to do it?
Since you didn't provide any code in your question, please allow me to give you a general answer in response..
What you're looking for are two date/times, today and tomorrow, both specified a "0 hours, 0 minutes, 0 seconds".
today <= date AND date < tomorrow
Note the two different comparisons.
The simplest technique would be to use DateFormat:
String input = "Sat Feb 17 2013";
Date date = new SimpleDateFormat("EEE MMM dd yyyy", Locale.ENGLISH).parse(input);
long milliseconds = date.getTime();
String input1="Sun Feb 18 2013"
Date inputNextDay = new SimpleDateFormat("EEE MMM dd yyyy", Locale.ENGLISH).parse(input);
long millisecondsForNextDay=inputNextDay.getTime();
To get the rows that fall on a particular day, just find rows having milliseconds value of timestamp between milliseconds and millisecondsForNextDay:
if(rowsTimestampSeconds>milliseconds && rowsTimestampSeconds<millisecondsForNextDay){
//get the row
}
You can use the GregorianCalendar class to do this without any strings.
Calendar calendar = new GregorianCalendar(year, month, day);
long start_of_day_millis = calendar.getTimeInMillis();
calendar.add(Calendar.DAY_OF_MONTH, 1);
long next_day_millis = calendar.getTimeInMillis();
The reason to use calendar.add(Calendar.DAY_OF_MONTH); instead of just adding (24*60*60*1000) to the first millisecond value is to account for leaps. Beyond the commonly known leap year, there are occurrences of leap seconds. See this link: http://www.timeanddate.com/time/leapseconds.html
Make sure that your expression is inclusive of start_of_day_millis (greater than or equal) and exlusive of next_day_millis (lesser than).
i.e: if(test_time >= start_of_day_millis && test_time < next_day_millis)
Update:
If you want today's date you can omit all the parameters in the calendar constructor and just call new GregorianCalendar(), but you will need to ensure that the hour, minute, second and millisecond fields are being zeroed out with calls to calendar.set(Calendar._FIELD_NAME_, 0); afterwards, before you use the calendar, because it would be initialized to the exact moment the object is created.
You should be using a date-time type for your database column to store date-time data rather than an integer of milliseconds. The SQL standard defines a few date-time types. But support for date-time varies widely, with Postgres being one of the best.
Since you tagged Java, read this Question and my Answer to learn about using Java to pinpoint the first moment of today and tomorrow. The Half-Open approach used there is common in date-time work. Half-Open means a span of time where the beginning is inclusive while the ending is exclusive. For SQL, it means not using the BETWEEN operator.
The java.time framework is built into Java 8 and later, and back-ported to Java 6 & 7 and to Android. Read my other Answer for details.
Get the first moments of today and tomorrow. Be aware that time zone is crucial in determining dates and the meaning of “today”. For any given moment, the date varies around the world by time zone. A new day begins earlier in the east. For example, a few moments after midnight in Paris is still “yesterday” in Montréal.
Instant instant = Instant.now();
ZoneId zoneId = ZoneId.of( "America/Montreal" );
ZonedDateTime zdt = ZonedDateTime.ofInstant( instant , zoneId );
ZonedDateTime zdtStart = zdt.toLocalDate().atStartOfDay( zoneId );
ZonedDateTime zdtTomorrowStart = zdtStart.plusDays( 1 );
Some day we may see JDBC drivers updated to directly handle java.time types. Indeed, JDBC 4.2 compliant drivers may work if you call getObject and setObject on your ResultSet and PreparedStatement respectively. But if not, fallback to using the java.sql types. Notice the new methods added to these old classes including java.sql.Timestamp. The from method takes an Instant which we can extract from our ZonedDateTime objects.
java.sql.Timestamp tsStart = java.sql.Timestamp.from( zdtStart.toInstant() );
java.sql.Timestamp tsStop = java.sql.Timestamp.from( zdtTomorrowStart.toInstant() );
Now set these two variables are arguments on your PreparedStatement. Notice the comparison operators, testing for possible values that start on first moment of the day (>=) and running up to but not including the first moment of the next day (<).
String sql =
"SELECT * FROM event_" +
"WHERE when_ >= ? " +
"AND when_ < ? " +
";" ;
…
pstmt.setTimestamp( 1 , tsStart );
pstmt.setTimestamp( 2 , tsStop );
If you do indeed store integers instead of using date-time types, and you are storing milliseconds as a count from the epoch reference date-time of first moment of 1970 in UTC, then you can extract a number from each Instant. Remember that the java.time classes use a finer resolution of nanoseconds as do some databases such as H2 Database, and some databases such as Postgres capture date-time with a resolution of microseconds. So truncating to milliseconds may mean a loss of data.
long millisStart = tsStart.toInstant().toEpochMilli();
long millisStop = tsStop.toInstant().toEpochMilli();
Call setLong on your PreparedStatement.
pstmt.setLong( 1 , millisStart );
pstmt.setLong( 2 , millisStop );
The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.
The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.
Where to obtain the java.time classes?
The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.
