Category: Uncategorized

Date versus Date/Time

It can be tempting to just default the data type to date/time, because you could always put the time portion as 00:00:00.000 or 23:59:59.999 or the current time. But as your product grows and branches out into more time zones and connects with other software products, you will mightily regret this decision.

As your software begins to exchange information with other software, you can get into situations where the other software only wants a date – not a date/time. And – the other software is located in a different time zone. Or one uses UTC and the other uses local time.

So when the date converts, due to the time zone difference, it can cross midnight. If the receiving software is using a date, instead of a date/time – it chops off the time portion and now you have a date that is a day later or earlier than it was in the originating piece of software.

The time to catch this is when you are designing the database schema in the first place. What kind of a date are you dealing with? Is it some kind of date that determines whether the data falls into one accounting period or another? If so, you want 22 March 2026 to be 22 March 2026 whether the database saving the date is in Hawaii, India or the Shetland Islands. Or is it the point in time when the record was saved to the database? In this case, you want time parts. And when this date is displayed (if it ever is) it will convert to the local time, which is fine. No matter who is looking at this date in any part of the world, they will understand the same point-in-time that the date/time is representing.

If you just set everything to date/time thinking “What the hey it covers all the bases”, then when your accounting application is 10 years old and running all over the globe, you will be angry with your former lazy, schema-designing self.

And save your dates in UTC. It will make everything easier in the long run.

Writing timestamps to database with two servers in different time zones

blueFlowers027Watermarked

Reference:

The problem

I have two different applications accessing the same database.  One runs on a server in the Pacific Time Zone.  The other, newer app runs on a server using UTC.  The two applications were writing timestamps to the database with different time zones.  Those two times are either 7 or 8 hours difference, depending on whether we’re in Daylight Savings Time or Standard Time.

To make matters worse, comparisons needed to be made between dates written by the two different applications.  Clearly they needed to be using the same time zone.

The handling

Probably many ways to handle, and this may not even be the best way.  But it is a way that worked.

The newer app on the UTC server uses Springboot.

  • Set up the time zone ID as a parameter in the application properties YAML file.  The time zone ID to use in this case is “America/Los_Angeles”.  Looks like this:
zoneid: America/Los_Angeles
  • In the classes that need to set the timestamp, declare a string variable to hold the time zone ID.  Annotate it with @Value so it looks like this:
@Value("${zoneid}")
private String zoneId;
  • Add a method that runs when the class is first initialized, to establish the time zone.
@PostConstruct
void started() {
    TimeZone.setDefault(TimeZone.getTimeZone(zoneId));
}

When the entity is written to the database, the timestamps will have the local time, in this case Pacific Time.

If I weren’t trying to keep backward compatibility with a legacy system, I would prefer writing the timestamps to the database using UTC time zones.  Then the displaying software can determine what local time zone to display them in.

 

Deleting large numbers of old files on Windows

joyinspringwatermarked

When you have a large quantity of files to delete, it can be unwieldy doing this via the Windows interface.  You can open up a command window in administrative mode (in case administrative mode is needed to delete some of the files you want to delete) and issue the following command:

forfiles /p "d:\Temp" /s /m *.* /D -10 /C "cmd /c del @path /F /Q"

/p = the path to file files
/s = look in subfolders
/m = file mask to match when looking for files
/D - = search for files n days before current date
/C = the command to execute on the files
cmd /c = precede the command with this
del = delete the file
/F = force deletion of read-only files
/Q = don't ask to confirm deletion when using a wildcard

UTC date/time string to Java date in local time zone

mushroom

Reference: https://www.w3.org/TR/NOTE-datetime

Uses joda-time (because yes, I still work in Java 1.6).  There is probably a simpler way.  But this works. This assumes all parts of the string are supplied.

public static Date convertUTCStringToLocalDateTime(String utcDate)
{
    DateTime utcDateTime = new DateTime(utcDate);
    Date localDateTime = utcDateTime.toDate();
    return localDateTime;
}