Cookbook/Timezones

From Kx Wiki
Jump to: navigation, search

The wiki is moving to a new format and this page is no longer maintained. You can find the new page at code.kx.com/q/cookbook/timezones.

The wiki will remain in place until the migration is complete. If you prefer the wiki to the new format, please tell the Librarian why.

Timezones (TZ) and Daylight Savings Time (DST)

kdb+ has 2 built in functions ltime and gtime which can be used to get the GMT time or localtime according to the TZ shell environment setting.

One solution for more comprehensive timezone calculations is to have a table that contains the timezones, their gmt offsets, and the datetime of any dst changes. e.g.

timezoneID    gmtDateTime                   gmtOffset            dstOffset
-------------------------------------------------------------------------------------
Europe/Zurich 2006.10.29D01:00:00.000000000 0D01:00:00.000000000 0D00:00:00.000000000
Europe/Zurich 2007.03.25D01:00:00.000000000 0D01:00:00.000000000 0D01:00:00.000000000
Europe/Zurich 2007.10.28D01:00:00.000000000 0D01:00:00.000000000 0D00:00:00.000000000
Europe/Zurich 2008.03.30D01:00:00.000000000 0D01:00:00.000000000 0D01:00:00.000000000
Europe/Zurich 2008.10.26D01:00:00.000000000 0D01:00:00.000000000 0D00:00:00.000000000
Europe/Zurich 2009.03.29D01:00:00.000000000 0D01:00:00.000000000 0D01:00:00.000000000
Europe/Zurich 2009.10.25D01:00:00.000000000 0D01:00:00.000000000 0D00:00:00.000000000
Europe/Zurich 2010.03.28D01:00:00.000000000 0D01:00:00.000000000 0D01:00:00.000000000
Europe/Zurich 2010.10.31D01:00:00.000000000 0D01:00:00.000000000 0D00:00:00.000000000
Europe/Zurich 2011.03.27D01:00:00.000000000 0D01:00:00.000000000 0D01:00:00.000000000
Europe/Zurich 2011.10.30D01:00:00.000000000 0D01:00:00.000000000 0D00:00:00.000000000
Europe/Zurich 2012.03.25D01:00:00.000000000 0D01:00:00.000000000 0D01:00:00.000000000
Europe/Zurich 2012.10.28D01:00:00.000000000 0D01:00:00.000000000 0D00:00:00.000000000

and then using 3 functions

lg:{[tz;z] exec gmtDateTime+adjustment from aj[`timezoneID`gmtDateTime;([]timezoneID:tz;gmtDateTime:z);t]};
gl:{[tz;z] exec localDateTime-adjustment from aj[`timezoneID`localDateTime;([]timezoneID:tz;localDateTime:z);t]};
ttz:{[d;s;z]lg[d;gl[s;z]]}

one can transform between local time and gmt time and vice-versa, for any specified timezone.

e.g.

q)lg[enlist `$"Europe/Zurich";enlist 2010.03.28D01:00:00.000]
,2010.03.28D03:00:00.000000000
q)gl[enlist `$"Europe/Zurich";enlist 2010.03.28D03:00:00.000]
,2010.03.28D01:00:00.000000000

and local times between timezones

q)show ttz[enlist `$"America/New_York";enlist `$"Europe/Zurich";enlist 2010.03.28D03:00:00.000]
,2010.03.27D21:00:00.000000000
q)show ttz[enlist `$"America/New_York";enlist `$"Europe/Zurich";enlist .z.P]
,2010.01.20D07:00:08.088411000

The timezone information can be generated using a brute force approach in java, and written to a csv file using

#!java
package dst;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.TimeZone;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Main {
    public static void main(String[] args) {
        PrintWriter out = null;
        try {
            out = new PrintWriter(new FileWriter("tzinfo.csv"));
            out.println("timezoneID,gmtDateTime,gmtOffset,dstOffset");
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy.MM.dd'D'HH:mm:ss.SSS");
            formatter.setTimeZone(TimeZone.getTimeZone("GMT"));
            String[] timezones = TimeZone.getAvailableIDs();
            for (int i = 0; i < timezones.length; i++) {
                TimeZone tz = TimeZone.getTimeZone(timezones[i]);
                boolean prevDst = false;
                Calendar cal = GregorianCalendar.getInstance(tz);
                cal.clear();
                cal.set(1970, 0, 1);
                int nMins=365 * 60 * 60 * 24;
                for (int j = 0; j < nMins; j++) {
                    cal.add(Calendar.MINUTE, 1);
                    boolean dst = tz.inDaylightTime(cal.getTime());
                    if ((dst != prevDst) || j==0) {
                        out.println(timezones[i] + "," + formatter.format(cal.getTime()) + "," +
                                    tz.getRawOffset()/1000 + "," + (dst ? tz.getDSTSavings()/1000 : 0));
                        prevDst = dst;
                    }
                }
            }
        } catch (IOException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            out.close();
        }
    }
}

and import into kdb+ and save to a kdb+ binary file using

q)t:("SPJJ";enlist ",")0:`:tzinfo.csv;
q)update gmtOffset:`timespan$1000000000*gmtOffset,dstOffset:`timespan$1000000000*dstOffset from `t;
q)update adjustment:gmtOffset+dstOffset from `t;
q)update localDateTime:gmtDateTime+adjustment from `t;
q)`gmtDateTime xasc `t;
q)update `g#timezoneID from `t;
q)`:tzinfo set t; / save file for easy distribution

and the for use later, one needs only

q)t:get`:tzinfo;
q)lg:{[tz;z] exec gmtDateTime+adjustment from aj[`timezoneID`gmtDateTime;([]timezoneID:tz;gmtDateTime:z);t]};
q)gl:{[tz;z] exec localDateTime-adjustment from aj[`timezoneID`localDateTime;([]timezoneID:tz;localDateTime:z);t]};
q)ttz:{[d;s;z]lg[d;gl[s;z]]}

Note that the most recent version of java should be used to ensure that the latest timezone database is being used.

Alternatively you can use combination of /usr/share/zoneinfo/zone.tab and zdump Unix command

Example

Personal tools
Namespaces
Variants
Actions
Navigation
Print/export
Toolbox