3

We store a date in a sqlserver db table as varchar. When this is read in the java code as a String and then parsed to a Date, it gets read as UTC (java code is in servers that are in UT). And on reconverting the date to ET, it goes 4 hours behind. How do I handle storing the date in ET in this db column so it gets read as ET in the java code.

We are researching around offsets, but not understanding exactly what to do.

Varchar date in table 03/29/2019 23:23:03 //we want this date to be in ET

SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
Date beginDate = sdf.parse("03/29/2019 23:23:03");

//The problem is when this code executes, the server is in UTC. So beginDate //is read as 03/29/2019 23:23:03 UTC instead of 03/29/2019 23:23:03 ET

Expected 03/29/2019 23:23:03 ET Actual 03/29/2019 23:23:03 UTC

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
Gayatri
  • 152
  • 3
  • 12
  • 3
    Are aware that storing date/time values in varchar fields is an antipattern? Are you also aware that most timestamps (for past or present values) *should* be stored in UTC? Please review https://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices – Matt Johnson-Pint May 14 '19 at 16:55
  • Also [Java Best Practice for Date Manipulation/Storage for Geographically Diverse Users](https://stackoverflow.com/questions/40075780/java-best-practice-for-date-manipulation-storage-for-geographically-diverse-user) – Ole V.V. May 14 '19 at 18:13
  • I recommend you don’t use `SimpleDateFormat` and `Date`. Those classes are poorly designed and long outdated, the former in particular notoriously troublesome. Instead use `ZonedDateTime`, `DateTimeFormatter` and `Instant`, all from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). – Ole V.V. May 14 '19 at 18:15

3 Answers3

4

First, you need to be aware that a Date object doesn't have a time zone at all. It's just an instant in time. So even when you've parsed the value correctly, it'll represent the right instant in time, but you may need to convert it back to Eastern Time later on.

Second, you need to be aware that storing values like this introduces ambiguity - if you store a value of (say) 11/03/2019 01:30, that local time occurs twice - once before the daylight saving transition and once afterwards. If you're always storing times in the past, you should at least consider storing UTC instead - although that's not always the right answer, particularly not if you're storing future date/time values.

For the parsing part, you just need to set the time zone in the Calendar used by SimpleDateFormat. For example:

SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss", Locale.US);
sdf.setTimeZone(TimeZone.getTimeZone("America/New_York");
Date beginDate = sdf.parse("03/29/2019 23:23:03");

Finally, I'd strongly advise you to start migrating your code to use java.time if at all possible. It's a much nicer API than the java.util.Date etc API.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • 1
    Thank you for the answer. Our requirement wants us to store date in ET in the db (literally not the timezone, but as an example, if we want our job to run on 01/23/2019 13:00 ET, we want to store 01/23/2019 13:00 in db so that it runs at the time even if the server that reads it is in UTC). So, are you saying that if we give 01/23/2019 13:00 in db and use sdf.setTimeZone(TimeZone.getTimeZone("America/New_York"); it will read the date in ET instead of UTC? – Gayatri May 14 '19 at 17:09
  • 4
    @Gayatri: It will parse the date *as Eastern Time* - but the resulting `Date` is still just an instant in time (2019-01-23T08:00Z). I would suggest at least talking to whoever is providing this requirement and explain the downsides of the "store as varchar in Eastern Time" so that it might be reconsidered in future. – Jon Skeet May 14 '19 at 17:21
  • 1
    Let me emphasize that, indeed, this "store as varchar in Eastern Time" plan is a *really* bad plan, as Jon Skeet commented. Generally best to store in UTC, then adjust to a time zone for presentation to user. – Basil Bourque May 14 '19 at 17:53
2

java.time

The Answer by Jon Skeet is correct. As he mentioned, you should be using the java.time classes defined by JSR 310. These modern classes years ago supplanted the terrible date-time classes such as Date and SimpleDateFormat. Here is some example code in that direction.

Varchar date in table 03/29/2019 23:23:03 //we want this date to be in ET

Parse the string as a LocalDateTime because our input lacks an indicator of offset-from-UTC or time zone.

Define a formatting pattern to match the input.

DateTimeFormatter f = DateTimeFormatter.ofPattern( "MM/dd/uuuu HH:mm:ss" ) ;

Parse.

LocalDateTime ldt = LocalDateTime.parse( input , f ) ;

If you are absolutely certain this date and time was intended for a particular time zone, apply a ZoneId to get a ZonedDateTime. 

ZoneId z = ZoneId.of( "America/New_York" ) ;
ZonedDateTime zdt = ldt.atZone( z ) ;

Adjust to UTC by extracting an Instant.

Instant instant = zdt.toInstant() ;

Your JDBC driver may not accept a Instant. So convert to an OffsetDateTime where the offset is set to zero hours-minutes-seconds (in other words, UTC itself). 

OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;

Write to a column of type TIMESTAMP WITH TIME ZONE in your database. As of JDBC 4.2 and later, we can directly exchange java.time objects with a database. 

myPreparedStatement.setObject( … , odt ) ;

And retrieval. 

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

Adjust to your desired time zone. 

ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;

Now you have the pieces needed to do some database-refactoring, to replace that varchar column with a proper TIMESTAMP WITH TIME ZONE column.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
2
  1. Use a datetime datatype of your database engine (DBMS), not varchar for date and/or time. With many DBMSs, the timestamp with timezone type defined in the SQL standard is the correct type to use. I don’t know SQL Server well enough to tell precisely what you should use there. Make sure you store date and time in UTC.
  2. If you cannot get around the requirement to store as varchar, store in ISO 8601 format in UTC. For example 2019-03-30T03:23:03Z.
  3. If you also cannot get around the requirement to store in Eastern Time, make sure you are clear whether North American Eastern Time or Australian Eastern Time is intended. Store date and time with UTC offset, for example 2019-03-29T23:23:03-04:00.
  4. If you also cannot get around the requirement to store in the format 03/29/2019 23:23:03 (without offset), be aware that in the fall when summer time (DST) ends and the clocks are moved back, you are storing ambiguous times.

Under all circumstances prefer java.time, the modern Java date and time API. The answer by Basil Bourque shows you how, I don’t need to repeat that.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
Ole V.V.
  • 81,772
  • 15
  • 137
  • 161