How do I increment a java.sql.Timestamp by 14 days?

Question

I have an app that takes a Timestamp as a boundary for the start date and end date of a sql selection, I want to populate a hashmap with weeks this year since the first monday of the year as the values and the week number as the keys. I'm finding it really hard to work with timestamps and I don't feel very good about adding 86,400,000 seconds to it to increment the day, as this doesn't account for the leap days, hours, seconds.

I plan on adding 13 days 23 hours, 59 minutes and 59 seconds to it so that I can lookup the start date in the map by the week as the key, then use the start date to get the end date.

So I'm looking to try to get something like this:

Week  startDate              endDate
1     2011-01-03 00:00:00    2011-01-16 23:59:59
2     2011-01-17 00:00:00    2011-01-30 23:59:59

With the first two columns in the Map and the last one being calculated after looking it up. How do I safely increment a java.sql.Timestamp?

The only problem you'd have to worry about is leap seconds, and those happen once every few years. If your program had been running since 1970 it would be off by a total of 24 seconds. Your plan of adding 86.4M seconds is perfectly viable. — corsiKa, Sep 16 '11 at 20:34
How about working with Calendar or Joda? http://joda-time.sourceforge.net/ — , Sep 16 '11 at 20:34
I guess the real question is... have you identified this as a blocker for your app? If you haven't, just go with it. If it is a problem, it will present itself and you can tackle it then. — corsiKa, Sep 16 '11 at 20:36
not an answer for your question but a suggestion to try something out: you mentioned that the timestamp is used as a boundary for start date and end date. instead of calculating the end date timestamp in java and then passing it in your query, why not pass the start date timestamp that you have and then make the end date timestamp be calculated with database date/time functions based on it? — , Sep 16 '11 at 20:50
Is there some reason you need an exact, inclusive `timestamp`? Why not use an inclusive->exclusive range (`date` or `timestamp`) instead? So, `>= startDate AND < endDate` - then just add 14 days, and you don't have to worry about seconds at all... — Clockwork-Muse, Sep 16 '11 at 20:58

score 57 · Answer 1 · edited Sep 02 '15 at 08:01

57

java.sql.Timestamp ts = ...
Calendar cal = Calendar.getInstance();
cal.setTime(ts);
cal.add(Calendar.DAY_OF_WEEK, 14);
ts.setTime(cal.getTime().getTime()); // or
ts = new Timestamp(cal.getTime().getTime());

This will correctly cater for daylight-time transitions in your default Timezone. You can tell the Calendar class to use a different Timezone if need be.

edited Sep 02 '15 at 08:01

wvdz

16,251
4
53
90

answered Sep 16 '11 at 20:43

Adrian Pronk

13,486
7
36
60

This will only correctly account for daylight savings iff your VM has an updated listing. JodaTime allows the use of a standard source for this - supposedly the VM updates for this have been historically slow. – Clockwork-Muse Sep 16 '11 at 20:55
1

I suspect that one reason to the slow updates is that "enterprisy" users stay on jvms that are at least 10 years old... On the other hand, most OS manage to keep a list of timezone info that has all the info that's needed. The jre should really use that. On my linux laptop, it seems to be setup until 2499... – KarlP Sep 16 '11 at 21:05

score 9 · Accepted Answer · answered Sep 16 '11 at 20:34

9

It worth noting that 14 days is not always 14 * 24 * 3600 seconds. When you have daylight savings, this can be an hour shorter or longer. Historically it can be much more complex than that.

Instead I would suggest using JodaTime or the Calendar to perform the time zone dependant calculation.

answered Sep 16 '11 at 20:34

Peter Lawrey

525,659
79
751
1,130

1

Would that really matter though? As long as you're using UTC you won't run into this problem. – corsiKa Sep 16 '11 at 20:35
1

SQL mostly doesn't use UTC. Especially if you don't explicitly metntion it. – Adrian Pronk Sep 16 '11 at 20:38
If you are using GMT or any timezone without daylight savings it not a problem. I wanted to flag that 14 days might contain a day which doesn't have 24 hours. Java doesn't support leap seconds like GMT but UTC does. ;) – Peter Lawrey Sep 16 '11 at 20:43

Anders B · Answer 3 · 2023-04-19T09:15:33.593

8

Java 8

Timestamp old;
ZonedDateTime zonedDateTime = old.toInstant().atZone(ZoneId.of("UTC"));
Timestamp newTimestamp = Timestamp.from(zonedDateTime.plus(14, ChronoUnit.DAYS).toInstant());

edited Apr 19 '23 at 09:15

answered May 29 '17 at 12:12

Anders B

3,343
1
26
17

is it `java.security.Timestamp`? `java.sql.Timestamp`? – Eugene A Feb 21 '19 at 12:05
@EugeneA It is `java.sql.Timestamp`. – Boss Man May 17 '19 at 19:44
2

Why did you need the ZonedDateTime? `Timestamp new = Timestamp.from(old.toInstant().plus(14, ChronoUnit.DAYS));` – Maxime Chéramy Aug 20 '19 at 09:41
1

`new` is a reserved keyword – Alex Vergara Apr 03 '23 at 07:44

score 6 · Answer 4 · answered Oct 21 '15 at 11:44

private Long dayToMiliseconds(int days){
    Long result = Long.valueOf(days * 24 * 60 * 60 * 1000);
    return result;
}

public Timestamp addDays(int days, Timestamp t1) throws Exception{
    if(days < 0){
        throw new Exception("Day in wrong format.");
    }
    Long miliseconds = dayToMiliseconds(days);
    return new Timestamp(t1.getTime() + miliseconds);
}

score -1 · Answer 5 · edited Oct 11 '21 at 02:18

-1

Timestamp my14DaysAfter = Timestamp.valueOf(myTimestamp.toLocalDateTime().plusDays(14));

edited Oct 11 '21 at 02:18

Shaido

27,497
23
70
73

answered Oct 08 '21 at 15:21

Pipo

4,653
38
47

Shouldn't there be a minus there? – Wai Ha Lee Oct 09 '21 at 09:46
question is increment.... but there exist a minusDays too... – Pipo Oct 09 '21 at 15:44
@WaiHaLee changed it thx! – Pipo Oct 11 '21 at 01:20

How do I increment a java.sql.Timestamp by 14 days?

5 Answers5

Linked

Related