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

Python try catch loop with increasing wait time

rosebud

The idea here is if there is an occasional error that causes your whole procedure to abort, but it is just an intermittent glitch that will work just fine if you wait a bit, you can just trap for the error and retry a few times.

But each time you retry, the wait time increases, since you don’t exactly know when the issue will clear.  This is with Python 2.7.10.

import time

tries = 1
wait = [1, 5, 10, 15]
while tries <= 5:
    try:
        raise ValueError('Oops!')
        break
    except:
        if tries < 5:
            print "Retry after " + str(wait[tries - 1]) + " seconds."
            time.sleep(wait[tries - 1])
    finally:
        tries = tries + 1

 

Here’s the output:

Retry after 1 seconds.
Retry after 5 seconds.
Retry after 10 seconds.
Retry after 15 seconds.

In real life, the differences between the wait times are bigger.  I did 1 minute, 5 minutes, 30 minutes, and an hour.

Also of course you would have your real code inside the try/except.  The raise ValueError(‘Oops!’) is just to test the concept of the try/catch loop with increasing wait times.

If you know the exact exception, you can trap for it specifically, like below:

except ValueError as e:

where ValueError is substituted with the actual exception you are trapping for.