Get correct Date and Time (timezone, daylight) from SQLite Timestamp

Question

Ok, I'm gonna be crazy. I have tested many solutions, without the correct result!

Let me explain: I've created an application with SQLite database. On a table, I record some informations about user's current location, like latitude, longitude AND timestamp!

I choose timestamp because it is "format agnostic". I format this later on display.

Here's the beginning of the problem.

I create a function that convert timestamp to string representation of date and time. Easy ? Yes, it's that I thought before.

This is the function (with some garbage tests) :

private String getDate(Timestamp timestamp) {
    /*Calendar cal = Calendar.getInstance(TimeZone.getDefault(), Locale.getDefault());
    cal.setTimeInMillis(timestamp.getTime());
    String date = DateFormat.format("dd/MM/yyyy HH:mm", cal).toString();*/

    //Time time = new Time(Time.getCurrentTimezone());
    /*Time time = new Time();
    time.set(timestamp.getTime());
    time.switchTimezone(Time.getCurrentTimezone());
    //String date = time.format3339(false);
    String date = time.format2445();*/

    //String date = DateFormat.getDateFormat(this).format(new Date(timestamp.getTime()));
    //String date = DateUtils.formatDateTime(this, timestamp.getTime(), DateUtils.FORMAT_NUMERIC_DATE | DateUtils.FORMAT_SHOW_DATE | DateUtils.FORMAT_SHOW_TIME);
    String date = DateFormat.getDateFormat(this).format(new Date(timestamp.getTime())) + " " + DateFormat.getTimeFormat(this).format(new Date(timestamp.getTime()));

    return date;
}

The problem is that the displayed time is incorrect! For example, the timestamp was today, at 21:01, but it displays something like "09/04/2014 19:01". Two hours difference!

In every solution that I test, the difference is identical. Why two hours? It's UTC time, I guess. But I pay attention to timezone.

It's important to notice that I have the location where the timestamp was generated. Maybe he could be used to determine the correct timezone?

Any suggestions or solutions (even an not too easy)?

PS: I lived in Switzerland.

EDIT:

The timestamp is created automatically when an entry is created ('CREATION_TIMESTAMP' TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL).

Here the two functions I use to read the location on database:

public Location getLocation(long id) {
    Cursor cursor = db.query(LocationSQLiteHelper.TABLE_NAME, allColumns, LocationSQLiteHelper.COLUMN_ID + " = " + id, null, null, null, null);

    cursor.moveToFirst();

    return cursorToLocation(cursor);
}

private Location cursorToLocation(Cursor cursor) {
    Location location = new Location();

    location.setId(cursor.getLong(0));
    location.setLatitude(cursor.getDouble(1));
    location.setLongitude(cursor.getDouble(2));
    location.setAccuracy(cursor.getFloat(3));
    location.setCreationTimestamp(Timestamp.valueOf(cursor.getString(4)));

    return location;
}

Maybe should I use a date instead? But it could be format's problem, no?

Where are you testing, emulator? If yes, check emulator's time. — isah, Apr 09 '14 at 19:27
SQLite time stamps do not contain time zone information. The functions for both saving and loading timestamps must work consistently; show them. — CL., Apr 09 '14 at 19:40
@JonSkeet In SQLite, the value is a timestamp. Example : 1397060298000 that represents 09/04/2014 20:18, but displayed as 18:18! — Kevin Vuilleumier, Apr 10 '14 at 07:11
@KevinVuilleumier: That timestamp represents 16:18 UTC. In what time zone did you think it was meant to be 20:18? In other words, I suspect the problem may well be with how you're populating the field... — Jon Skeet, Apr 10 '14 at 07:12
@JonSkeet I have update my post, if it could help you. I'm in Switzerland, that's UTC+2. Mmmh... I see that the Timestamp class has some deprecated methods to interact with timezone... — Kevin Vuilleumier, Apr 10 '14 at 07:23

score 0 · Answer 1 · edited May 23 '17 at 11:49

I found a solution! The fact is that the timestamp is UTC and no information about timezone is stored.

My function getDate() is now like that (solution suggested by https://stackoverflow.com/a/18692993/2416369):

private String getDate(Timestamp timestamp) {
    Calendar calendar = Calendar.getInstance();
    TimeZone tz = TimeZone.getDefault();

    calendar.setTimeInMillis(timestamp.getTime());
    calendar.add(Calendar.MILLISECOND, tz.getOffset(calendar.getTimeInMillis()));

    SimpleDateFormat sdf = (SimpleDateFormat)SimpleDateFormat.getDateTimeInstance();
    Date currenTimeZone = (Date)calendar.getTime();
    String date = sdf.format(currenTimeZone);

    return date;
}

Get correct Date and Time (timezone, daylight) from SQLite Timestamp

1 Answers1