Date as Int for "date key"

Question

I wonder how to go about creating an expression in Java that takes a date variable formatted as "1999-12-30 12:34:45" and converts it to an Int holding "19991230".

This is probably something others would appreciate, especially people extracting and cleaning up data for loading into a Data Warehouse.

My guess is one need to change date pattern, convert to string, truncate first part (8 chars) and convert to int.

Ideas?

(update) My bad in explaining the issue. The resulting int will service as a key/Fk id to a Date Dimension. A similar operation should be done for creating a key to a Time-of-Day Dimension. The int will reduce the size consumed in the facts table, in which these int keys would be stored. See related answer: https://stackoverflow.com/a/8416548/1132571

Answer 1

Pretty easy to do this using the SimpleDateFormat class. However, as others mentioned you probably should be storing the epoch timestamp as Database TimeStamp column instead of storing the date in an int form. It will cause problem later when you are manipulating the data with SQL.

public static void main(String[] args) throws ParseException {
        String dateStr = "1999-12-30 12:34:45";
        String formatStr = "yyyy-MM-dd HH:mm:ss";
        String formatStrOther = "yyyyMMdd";
        Date testDate = null;
        SimpleDateFormat sdf= new SimpleDateFormat(formatStr);
        SimpleDateFormat sdfOther= new SimpleDateFormat(formatStrOther);
        sdf.setLenient(false);
        testDate = sdf.parse(dateStr);
        Integer otherDate = Integer.valueOf(sdfOther.format(testDate));
        System.out.println("Newly formatted date in int is: " + otherDate); //prints Newly formatted date in int is: 19991230

    }

Answer 2

Simply remove special chars and then convert to int

Integer.parseInt("1999-12-30 12:34:45".replaceAll("[- :]", ""));

in your case you want only the date, not the time so it would be:

Integer.parseInt("1999-12-30 12:34:45".substring(0,10).replaceAll("-", ""));

I should mention that this only works if the original format is correct... and it is actually quite ugly, I would rather use a long and getTime - and maybe simpleDateFormat. You really do not want to throw away useful data!

Answer 3

getTime will give you a long with this information, int won't be large enough.

http://docs.oracle.com/javase/1.4.2/docs/api/java/util/Date.html#getTime()

Answer 4

While I agree with the commenters finding this is strange, if I really needed to transform a date into a long I'd use the date.getTime() which returns a long representing the amount of millisec since 01.01.1970.

The good stuff is that it can easily be turned back into a date (Java has a constructor for this, I suspect other languages do as well)

Answer 5

Assuming that the format remains the same:

String dateStr = "1999-12-30 12:34:45";
dateStr = dateStr.substring(0, 10);
dateStr = dateStr.replace("-", "");
int n = Integer.parseInt(dateStr);
System.out.println(n);

Prints: 19991230

Answer 6

you could just use something like:

var dateObj=new Date("1999-12-30 12:34:45");

and then all you need to do is get the value of the date object, like so:

var timeStamp=dateObj.valueOf();

this will give you milliseconds since 1970, a universal time system which is used by most computer systems.

to convert back use:

var newTime=new Date(timeStamp);

Answer 7

Use SimpleDateFormat, SimpleDateFormat.parse, SimpleDateFormat.format and Integer.parseInt [ http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html ]

1) create a SimpleDateFormat to parse the string into a Date object

2) create a SimpleDateFormat to format the Date object into the String representation of the in

3) use Integer.parseInt to change the newly formatted String into an int.

However, storing the date as an int for warehousing does not make much sense to me.

I guess that there is some restriction preventing you using either Date or a String in ISO 8601 format.

Answer 8

And another one. (I don't like parsing strings, too error prone with dates. ;-))

DateFormat format = new SimpleDateFormat("dd-MM-yyyy");

Date d;
try {

    d = (Date)format.parse("30-12-1999");
    Calendar c = Calendar.getInstance();
    c.setTime(d);

    int result = c.get(Calendar.YEAR) * 100*100 + 
                (c.get(Calendar.MONTH) + 1) * 100 + 
                 c.get(Calendar.DAY_OF_MONTH);

    System.out.println(result);

} catch (ParseException e) {
    e.printStackTrace();
}

Answer 9

Thanks for all suggestions, input and suggestions. This was what I was looking for:

Integer.parseInt(new SimpleDateFormat("yyyyMMdd").format(row.date));

Where row.date (from Talend Open Studio) holds the date I wanted to transform into an int date key.

Answer 10

In many ways you're better off leaving date keys as dates, although you may wish to truncate at days for the key. If you need the actual date-time as well, store it separately. There are a number of reasons for this, mainly to do with date arithmetic.

• Calculating date ranges on the fact table can be done without joining against the date dimension.

• Queries will return the date - this is quite efficient if you're just doing an ad-hoc query on the table.

• Special values for 'before', 'after', 'error' can be selected so they're still fairly obvious ('1800-01-01', '9000-01-01' '6666-01-01' etc.).

• Self-joins by date or date range are more efficient if you don't have to involve a date dimension.

• Most importantly, date arithmetic can be done directly on the table without having to join against the dimension. Calculating a date 180 days before '2012-01-15' is easier than doing it for 20120115. Also, SQL does not have a native conversion from 20120015 to the corresponding date without looking it up in a dimension.