Author: ltwede

Writing timestamps to database with two servers in different time zones



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:
private String zoneId;
  • Add a method that runs when the class is first initialized, to establish the time zone.
void started() {

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


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


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:
        raise ValueError('Oops!')
        if tries < 5:
            print "Retry after " + str(wait[tries - 1]) + " seconds."
            time.sleep(wait[tries - 1])
        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.

Getting SQL Replication Info Programmatically from Java

winterWisteria Wisteria leaves in winter.



SQL Server has a process called replication, whereby you can automatically copy records from one database to another, and keep them in-sync.  One use for this is to have one database as the transactional database, and another to report from, with the reporting database having indexes and constraints tailored more toward faster reporting (reads) than fast input.

Sometimes replication gets a little behind, and if you are running reports on the database that is a little behind, you might like to know.

The stored procedure sp_replcounters will tell you the count of transactions awaiting transfer to the distribution database (which lies between the transaction database and the destination database in a replication process).  You can call this stored procedure, but you want it to return the result set to Java.

One way to achieve this is to wrap your own stored  procedure around it, put the records into a temp table and query the temp table to return the result set.  This only works if you specify SET NOCOUNT  ON, otherwise the Java Persistence API (JPA) gets confused by the multiple queries and you don’t get the result set in your Java code.

CREATE PROCEDURE GetReplicationLatency


--This is important.  Results from multiple queries within stored procedures throw JPA off unless SET NOCOUNT ON is used.

declare @db varchar(50)
set @db = db_name()

create table #tempTable ([database] varchar(50), [replicated transactions] int, [replication rate trans/sec] int,
 [replication latency (sec)] decimal(16,6), replbeginlsn varchar(40), replnextlsn varchar(40))
insert into #tempTable
exec sp_replcounters
select * from #tempTable where [database] = @db

drop table #tempTable


Then your Java code looks like this:

private static final String REPLICATION_LATENCY_QUERY = "exec GetReplicationLatency ";Query query = getEntityManager().createNativeQuery(REPLICATION_LATENCY_QUERY);
Query query = getEntityManager().createNativeQuery(REPLICATION_LATENCY_QUERY);
List<Object[]> results = query.getResultList();