Asked  7 Months ago    Answers:  5   Viewed   46 times

I have to store UTC dateTime in DB.
I have converted the dateTime given in specific timezone to UTC. for that I followed the below code.
My input dateTime is "20121225 10:00:00 Z" timezone is "Asia/Calcutta"
My Server/DB(oracle) is running in the same timezone(IST) "Asia/Calcutta"

Get the Date object in this specific Timezone

        String date = "20121225 10:00:00 Z";
        String timeZoneId = "Asia/Calcutta";
        TimeZone timeZone = TimeZone.getTimeZone(timeZoneId);

        DateFormat dateFormatLocal = new SimpleDateFormat("yyyyMMdd HH:mm:ss z");
                    //This date object is given time and given timezone
        java.util.Date parsedDate = dateFormatLocal.parse(date + " "  
                         + timeZone.getDisplayName(false, TimeZone.SHORT));

        if (timeZone.inDaylightTime(parsedDate)) {
            // We need to re-parse because we don't know if the date
            // is DST until it is parsed...
            parsedDate = dateFormatLocal.parse(date + " "
                    + timeZone.getDisplayName(true, TimeZone.SHORT));
        }

       //assigning to the java.sql.TimeStamp instace variable
        obj.setTsSchedStartTime(new java.sql.Timestamp(parsedDate.getTime()));

Store into DB

        if (tsSchedStartTime != null) {
            stmt.setTimestamp(11, tsSchedStartTime);
        } else {
            stmt.setNull(11, java.sql.Types.DATE);
        }

OUTPUT

DB (oracle) has stored the same given dateTime: "20121225 10:00:00 not in UTC.

I have confirmed from the below sql.

     select to_char(sched_start_time, 'yyyy/mm/dd hh24:mi:ss') from myTable

My DB server also running on the same timezone "Asia/Calcutta"

It gives me the below appearances

  1. Date.getTime() is not in UTC
  2. Or Timestamp is has timezone impact while storing into DB What am I doing wrong here?

One more question:

Will timeStamp.toString() print in local timezone like java.util.date does? Not UTC?

 Answers

66

Although it is not explicitly specified for setTimestamp(int parameterIndex, Timestamp x) drivers have to follow the rules established by the setTimestamp(int parameterIndex, Timestamp x, Calendar cal) javadoc:

Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object. The driver uses the Calendar object to construct an SQL TIMESTAMP value, which the driver then sends to the database. With a Calendar object, the driver can calculate the timestamp taking into account a custom time zone. If no Calendar object is specified, the driver uses the default time zone, which is that of the virtual machine running the application.

When you call with setTimestamp(int parameterIndex, Timestamp x) the JDBC driver uses the time zone of the virtual machine to calculate the date and time of the timestamp in that time zone. This date and time is what is stored in the database, and if the database column does not store time zone information, then any information about the zone is lost (which means it is up to the application(s) using the database to use the same time zone consistently or come up with another scheme to discern timezone (ie store in a separate column).

For example: Your local time zone is GMT+2. You store "2012-12-25 10:00:00 UTC". The actual value stored in the database is "2012-12-25 12:00:00". You retrieve it again: you get it back again as "2012-12-25 10:00:00 UTC" (but only if you retrieve it using getTimestamp(..)), but when another application accesses the database in time zone GMT+0, it will retrieve the timestamp as "2012-12-25 12:00:00 UTC".

If you want to store it in a different timezone, then you need to use the setTimestamp(int parameterIndex, Timestamp x, Calendar cal) with a Calendar instance in the required timezone. Just make sure you also use the equivalent getter with the same time zone when retrieving values (if you use a TIMESTAMP without timezone information in your database).

So, assuming you want to store the actual GMT timezone, you need to use:

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
stmt.setTimestamp(11, tsSchedStartTime, cal);

With JDBC 4.2 a compliant driver should support java.time.LocalDateTime (and java.time.LocalTime) for TIMESTAMP (and TIME) through get/set/updateObject. The java.time.Local* classes are without time zones, so no conversion needs to be applied (although that might open a new set of problems if your code did assume a specific time zone).

Tuesday, June 1, 2021
 
julesj
answered 7 Months ago
80
  1. Read about time zone support in MySQL, and ensure your mysql database is configured with current time zone tables. Update regularly.

  2. Associate each location with a named IANA/Olson time zone, such as "America/Los_Angeles", or "Europe/London". Refer to the list here. If you have lat/lon, you can look up the time zone via one of these methods.

  3. Use the MySQL CONVERT_TZ function to convert the current UTC time to the specific zone. For example, CONVERT_TZ(UTC_TIMESTAMP(),'UTC','Asia/Tokyo')

  4. Use the day-of-week and time of day of the converted time to check against the day and range in the location's entry.

Also, note that others may suggest taking the approach of only storing UTC in the database, or of converting all of the values to UTC before comparing against the "now" value. Either of those approaches can fail in edge cases, since the UTC day-of-week is not necessarily the same day-of-week as in each time zone.

One other approach that will work, but takes more effort, is to predetermine the specific UTC starting and stopping times for some amount of time in the future (at least to the next one, but perhaps further). Then you can scan this list with the UTC time. This works better at scale, when you have thousands or more individual entries to check. But at smaller scales, it's usually not worth the overhead.

Likewise, you could have a background process that just sets a "now open" flag on each record, but it would have to constantly be working against your database, and you could never check for other times than "now".

Saturday, May 29, 2021
 
Manmay
answered 7 Months ago
70

ResultSet.getDate() returns a java.sql.Date, not a java.util.Date. It is defined to be a timeless date. If you want a timestamp, use ResultSet.getTimestamp()!

Thursday, July 29, 2021
 
superhero
answered 5 Months ago
80

I'd like to create a TimeZoneInfo from it. How is this possible?

It's not possible. A time zone offset is not the same thing as a time zone. Please read the timezone tag wiki, especially the section titled "Time Zone != Offset".

... then how can I convert UTC dates on the server side into the client's timezone using the minutes offset?

Create a DateTimeOffset that represents that moment in time. For example:

// From your database.  Make sure you specify the UTC kind.
DateTime utc = new DateTime(2013, 1, 1, 0, 0, 0, DateTimeKind.Utc);

// From JavaScript
int offsetMinutes = 420;

// Don't forget to invert the sign here
TimeSpan offset = TimeSpan.FromMinutes(-offsetMinutes);

// The final result
DateTimeOffset dto = new DateTimeOffset(utc).ToOffset(offset);

Also, make sure you understand that the offset you retrieved from the client in JavaScript is not necessarily the correct offset to apply to your database date. When you get the offset, it has to be for a particular moment in time. Since many time zones change offsets for daylight saving time, you cannot assume that the offset you currently have is appropriate for any particular value in your database. Therefore, while the above code does what you asked, it is probably still not a good idea in general.

Monday, August 2, 2021
 
hnkk
answered 4 Months ago
41

You decided to use JDBCTemplate most probably to simplify the code in comparison to plain JDBC.

This particular problem IMHO makes the plain JDBC solution as proposed in other answer much simpler, so I'd definitively recommend to get the database connection from JDBCTemplate and make the insert in a JDBC way.

The simplest solution using JDBCTemplate that comes to my mind is to wrap the insert in a PROCEDURE and return the timestamp as an OUT parameter.

Simple example (Adjust the time logik as required)

create procedure insert_with_return_time (p_str VARCHAR2, p_time OUT DATE) as
BEGIN 
   insert into identity_pk(pad) values(p_str);
   p_time := sysdate;
END;
/

The call is done using SimpleJdbcCall

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName("insert_with_return_time");
SqlParameterSource params = new MapSqlParameterSource().addValue("p_str", str);
Map<String, Object> out = jdbcCall.execute(params);

The Map contains the returned value e.g. [P_TIME:2019-10-19 11:58:10.0]

But I can only repeat, in this particular use case is IMHO JDBC a rescue from JDBCTemplate;)

Saturday, August 28, 2021
 
Aamir
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share