UTC Timestamp + Joda Time

Question

I am trying to get the UTC TimeStamp in a simple Java program using Joda:

public Timestamp getCurrentUTC(LocalDateTime date, DateTimeZone srcTZ, DateTimeZone dstTZ, Locale l) {
    DateTime srcDateTime = date.toDateTime(srcTZ);
    DateTime dstDateTime = srcDateTime.toDateTime(dstTZ);
    
    System.out.println("UTC Time:" + dstDateTime.getMillis());      
    System.out.println("UTC Time:" + new Timestamp(dstDateTime.getMillis()));
    
    return new Timestamp(dstDateTime.getMillis());
}

The output of the program is as follows:

UTC Time:1378265162047
UTC Time:2013-09-03 23:26:02.047

The millisecond value is the correct UTC time (i.e. confirmed with GMT-4 timezone) The second value is the EST timezone.

What I need is the UTC value unchanged as java.sql.Timestamp (ie TZ independent), for a database write. Is this possible?

Edit 1

DateTime srcDateTime = date.toDateTime(srcTZ);

DateTime dstDateTime = srcDateTime.toDateTime(dstTZ);

System.out.println("UTC Time:" + dstDateTime.getMillis());

I know that srcDateTime is the local date (GMT-4), and dstDateTime is UTC (GMT-0). Output values of the dates are as follows:

Source Date:2013-09-04T09:10:43.683-04:00

Destination Date: 2013-09-04T13:10:43.683Z

I tried all the combinations to try to get the UTC value of dstDateTime as a java.sql.TimeStamp:

System.out.println("UTC Time:" + dstDateTime.getMillis());
    
System.out.println("UTC Time:" + new Timestamp(srcDateTime.toDateTime(DateTimeZone.UTC).getMillis()));

System.out.println("UTC Time:" + new Timestamp(dstDateTime.toDateTime(DateTimeZone.UTC).getMillis()));

The print output for testing:

UTC Time:1378298760226 - Correct UTC

UTC Time:2013-09-04 08:46:00.226 - Incorrect Local Date Time instead of the Expected UTC

UTC Time:2013-09-04 08:46:00.226 - Incorrect Local Date Time instead of the Expected UTC

The first print line is the correct UTC timestamp. All I need is that same value as type java.sql.TimeStamp. Anything I tried always returned the local date time of the machine.

Edit 2

I tried the following:

System.out.println("UTC Timestamp:" + date.toDateTime(srcTZ).getMillis());
System.out.println("UTC Timestamp:" + new Timestamp(date.toDateTime(srcTZ).getMillis()));

The output is as follows:

UTC Time:1378342856315 - Correct UTC Time
UTC Timestap:2013-09-04 21:00:56.315 - Local Time other than the expected UTC Time

Whenever I try to convert to TimeStamp, I loose the valid UTC value that I am after.

In terms of the method's parameters:

srcTZ = DateTimeZone.forTimeZone(TimeZone.getTimeZone("America/Montreal")
dstTZ = DateTimeZone.forTimeZone(TimeZone.getTimeZone("Etc/UTC"))
Local l = new Locale("en", "CA")

Any help is greatly appreciated.

Nick.

Edit 3

Hello Matt,

Thank you so much for your response. We are getting the same results as you. Did not know the thing about printing etc.. More specifically:

System.out.println("UTC Timestamp:" + srcDateTime.toDateTime(dstTZ).getMillis());
System.out.println("UTC Timestamp:" + srcDateTime.toDateTime(dstTZ));
System.out.println("UTC Timestamp:" + new Timestamp(srcDateTime.toDateTime(dstTZ).getMillis()));

Yields The Output:

UTC Timestamp:1378389098468 - Correct UTC Timestap (Thu, 05 Sep 2013 13:51:38 GMT)
UTC Timestamp:2013-09-05T13:51:38.468Z - Correct UTC Time
UTC Timestamp:2013-09-05 09:51:38.468 - Local time is printed, UTC is expected

The problem was brought to my attention when we realized that the DB was storing the local time instead of UTC:

+---------------------+
| effectivedate       |
+---------------------+
| 2013-09-05 09:34:11 |
+---------------------+

The Mysql timezone is set to '-00:00'

mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2013-09-05 13:48:09 |
+---------------------+

Debugging the application using eclipse debugger we realized that the local date time (2013-09-05 09:51:38.468) was being passed to the DB (Can't post images, not enough points...). The datatype is straight TimeStamp, with no string manipulation. Maybe the eclipse debugger is using String.println() function as well, not sure..

I really appreciate all the help debugging our application. Did not want to take up so much time (no pun intended) and effort...

Kind Regards,

Nick.

"The millisecond value is the correct UTC time (ie GMT-4)" This statement doesn't make sense to me. Isn't UTC time always GMT-0? — Daniel Kaplan, Sep 04 '13 at 03:41
[this answer](http://stackoverflow.com/a/13539088/180100) might help — , Sep 04 '13 at 05:04
Hello tie, sorry, I fixed the working. I meant calculate against local TZ GMT-4. I am trying to get GMT-0 as TimeStamp. RC, toString will not work. We need it in timestamp. I know that toSTring returns the right value. — Nick Cameo, Sep 04 '13 at 12:50

score 24 · Accepted Answer · answered Sep 06 '13 at 17:14

24

I hope this saves someone 3 days of bullshit. Set the default timezone somewhere logical in your code. Makes things more portable that having to set an env variable etc.. You can do this by adding the following somewhere logical to your code, Constructor etc..:

DateTimeZone.setDefault(DateTimeZone.UTC);

You can print UTC, concatenate UTC whatever...

answered Sep 06 '13 at 17:14

Nick Cameo

419
1
4
10

6

Incase you are not using Joda time throughout your code, then you need to also set [`TimeZone.setDefault(TimeZone)`](http://docs.oracle.com/javase/7/docs/api/java/ut‌il/TimeZone.html#setDefault(java.util.TimeZone). You can also just set the user.timezone system property to get both in one pass. If you set TimeZone (using system property or calling the code) before touching any Joda code then it will pick up your setting. See my comment to my answer a few days ago for setting the `TimeZone`. – pickypg Sep 08 '13 at 01:44

score 6 · Answer 2 · answered Sep 05 '13 at 02:10

Try this short program, it should illustrate what is going on:

LocalDateTime date = LocalDateTime.now();
DateTimeZone tz = DateTimeZone.getDefault();

System.out.println(date);
System.out.println(tz);
System.out.println("-----");
System.out.println(date.toDateTime(tz));
System.out.println(date.toDateTime(tz).toInstant());
System.out.println(date.toDateTime(tz).toDateTime(DateTimeZone.UTC));
System.out.println("-----");
System.out.println(date.toDateTime(tz).getMillis());
System.out.println(date.toDateTime(tz).toInstant().getMillis());
System.out.println(date.toDateTime(tz).toDateTime(DateTimeZone.UTC).getMillis());
System.out.println("-----");
System.out.println(new Timestamp(date.toDateTime(tz).getMillis()));
System.out.println(new Timestamp(date.toDateTime(tz).toInstant().getMillis()));
System.out.println(new Timestamp(date.toDateTime(tz).toDateTime(DateTimeZone.UTC).getMillis()));

On my computer, it outputs:

2013-09-04T19:08:35.111
America/Phoenix
-----
2013-09-04T19:08:35.111-07:00
2013-09-05T02:08:35.111Z
2013-09-05T02:08:35.111Z
-----
1378346915111
1378346915111
1378346915111
-----
2013-09-04 19:08:35.111
2013-09-04 19:08:35.111
2013-09-04 19:08:35.111

As you can see, you can get to the UTC time with either .toInstant(), or with .toDateTime(DateTimeZone.UTC). But even if you call neither of these, you will still get the UTC value when you call getMillis().

So the problem isn't with JodaTime. The problem is in how you are evaluating your results.

When you create a java.sql.Timestamp, you're passing in the milliseconds from 1/1/1970 UTC. It's only when you display it that it is applying the local time zone to the result.

Assuming you are passing it to a database as a Timestamp and not doing some interm string representation, then you should be fine. Just because it looks like the local time when you call System.out.println, doesn't mean that it is a local time internally.

The java.sql.Timestamp class extends java.util.Date - which is where it gets this behavior from.

If your `Timestamp` is getting saved in local time, perhaps you are building your SQL query with string concatenation instead of passing proper parameters? Just a guess. — Matt Johnson-Pint, Sep 05 '13 at 14:47

score 1 · Answer 3 · answered May 08 '15 at 19:12

I struggled with this for hours and finally found a solution.

I'm using PlayFramework - not sure if this will help anyone, but for my database connection setting I had to use:

db.default.url="jdbc:mysql://localhost/continuum?characterEncoding=UTF-8&serverTimezone=UTC"

(add the '&serverTimezone=UTC')

score 0 · Answer 4 · answered Sep 04 '13 at 05:05

0

Do you need the UTC value or can you simply use the Timestamp.toString() method? this will give you a formatted time string for use in SQL statements.

answered Sep 04 '13 at 05:05

coderatchet

8,120
17
69
125

I know that the toString method will work, however we need it in java.sql.TimeStamp. – Nick Cameo Sep 04 '13 at 13:23

score 0 · Answer 5 · answered Sep 04 '13 at 05:23

0

LocalDateTime already excludes the timezone, which means that it either means literally the local time where the user enters "their" time (e.g., for a funeral invitation, which will always be in the local time) or it is already in UTC if you planned ahead.

If your date is already in the srcTZ's DateTimeZone, but loaded as LocalDateTime, then you should return

new Timestamp(date.toDateTime(srcTZ).toDateTime(DateTimeZone.UTC).getMillis());

Alternatively, if you are sure that the date is already in UTC, which is GMT+0, then you can return

new Timestamp(date.toDateTime(DateTimeZone.UTC).getMillis());

Finally, most databases accept the timestamp as an ISO formatted string, which is the default toString output:

date/*.toDateTime(srcTZ)*/.toDateTime(DateTimeZone.UTC).toString()

answered Sep 04 '13 at 05:23

pickypg

22,034
5
72
84

Hello Picky, please see above *edit*. I tried all combinations that you suggested, and all of them return the local time when converting to java.sql.timestamp not the needed UTC to write to the DB. – Nick Cameo Sep 04 '13 at 13:11
1

We do pass the LocalDateTime timezone excluded local time to the function however, do the correct offset within the function using DateTime: DateTime srcDateTime = date.toDateTime(srcTZ); DateTime dstDateTime = srcDateTime.toDateTime(dstTZ); – Nick Cameo Sep 04 '13 at 14:18
@NickCameo Time is particularly hard because of timezones. This is why I tend to always use UTC and convert to timezones as late as possible (at the presentation layer). To the point, if `date` is in a timezone such as EST/GMT-4, then you need to convert to a `DateTime` object that has that timezone, which should be the first step `toDateTime(srcTZ)`. At this point, you should have the actual date/time in its actual timezone. From there you don't actually need to do `toDateTime(DateTimeZone.UTC)`, but you should be able to use `getMillis()` as in `date.toDateTime(srcTZ).getMillis()`. – pickypg Sep 04 '13 at 16:48
Shy of that, can you give example values for your `date`, `srcTZ` and even the `dstTZ`? – pickypg Sep 04 '13 at 16:48
@ pickypg Please see "Edit 2" – Nick Cameo Sep 05 '13 at 01:16
Set your default `TimeZone` to be UTC/GMT: [`TimeZone.setDefault(TimeZone.getTimeZone("GMT"))`](http://docs.oracle.com/javase/7/docs/api/java/util/TimeZone.html#setDefault(java.util.TimeZone)). This only needs to be done once, preferably at startup. To avoid writing code, you can also set the [`"user.timezone"` system property](http://stackoverflow.com/a/2493805/706724), which will set it (use `-Duser.timezone=GMT` for UTC). – pickypg Sep 05 '13 at 17:49

UTC Timestamp + Joda Time

Edit 1

Edit 2

Edit 3

5 Answers5

Linked