Android timestamp wrong

Question

My timestamp displays 4 hour advance on the current time (e.g. 12:30pm is the current time, it displays 4:30pm) Can anyone help me how to change this? Here's the code:

public class MessagesDBAdapter {

    public static final String KEY_RECIPIENT = "recipient";
    public static final String KEY_MESSAGE = "message";
    public static final String KEY_TIME = "time";
    public static final String KEY_ROWID = "_id";

    private static final String TAG = "MessagesDBAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

     //Database creation sql statement

    private static final String DATABASE_CREATE =
             "create table notes (" + KEY_ROWID + " integer primary key autoincrement, "
        + KEY_RECIPIENT + " text not null, " + KEY_MESSAGE + " text not null, " + KEY_TIME + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP);";

    private static final String DATABASE_NAME = "data";
    private static final String DATABASE_TABLE = "notes";
    public static final String KEY_TIMESTAMP = "timeStamp";
    private static final int DATABASE_VERSION = 3;


    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS notes");
            onCreate(db);
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public MessagesDBAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    public MessagesDBAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDbHelper.close();
    }

    public long createNote(String phoneNo, String message) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_RECIPIENT, phoneNo);
        initialValues.put(KEY_MESSAGE, message);

        open();

        return mDb.insert(DATABASE_TABLE, null, initialValues);
    }

    public boolean deleteNote(long rowId) {

        return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    public Cursor fetchAllNotes() {

        return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_RECIPIENT,
                KEY_MESSAGE, KEY_TIME}, null, null, null, null, KEY_TIME + " DESC");
    }

    public Cursor fetchNote(long rowId) throws SQLException {

        Cursor mCursor =

            mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
                    KEY_RECIPIENT, KEY_MESSAGE, KEY_TIME}, KEY_ROWID + "=" + rowId, null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }

    public long saveMessages(Timestamp date, String phoneNo, String message) {
        ContentValues initialValues = createContentValues(date, phoneNo, message);
        return mDb.insert(DATABASE_TABLE, null, initialValues);
        }//

    private ContentValues createContentValues(Timestamp date, String phoneNo, String message) {
        ContentValues values = new ContentValues();
        values.put(KEY_TIME, date.toString());
        values.put(KEY_RECIPIENT, phoneNo);
        values.put(KEY_MESSAGE, message);
        return values;
        }//

    public Cursor getAllNotes() {

        Cursor mCursor = 

                mDb.rawQuery("SELECT _id, time, message, recipient, " +
                "datetime(timestampColumn, 'localtime') AS timestampColumn FROM notes", null);
        return mCursor;
    }

    public boolean updateMessage(long rowId, String phoneNo, String message) {
        ContentValues args = new ContentValues();
        args.put(KEY_RECIPIENT, phoneNo);
        args.put(KEY_MESSAGE, message);

        return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
}

Is there anyway of getting the current timestamp?

The below questions may be helpful to you: http://stackoverflow.com/questions/4068132/display-utc-date-time-into-date-time-according-to-the-current-time-zone , http://stackoverflow.com/questions/4286040/facing-problem-while-converting-date-to-milliseconds — Paresh Mayani, Dec 14 '11 at 06:16
Sounds like a timezone issue. Check the timezones match else you will need to convert them or ensure all times are being saved in the same timezone. — Steven, Dec 14 '11 at 06:19
@Paresh I used TIMESTAMP DEFAULT CURRENT_TIMESTAMP for the timestamp. It seems that they used other timestamp methods to get the timestamp. — Kev, Dec 14 '11 at 06:25
You can convert by looking up the timezone that was used and getting it's offset. Then use the desired timezone's offset to determine how many hours and minutes you need to adjust for. — Steven, Dec 15 '11 at 01:40

score 1 · Answer 1 · answered May 09 '13 at 12:09

1

use

System.currentTimeMillis();

to get the real timestamp in local zone. insert this along with other content values.

answered May 09 '13 at 12:09

oops.objective

1,264
1
8
8

Sunil Kumar Sahoo · Answer 2 · 2011-12-14T06:29:12.963

0

The default CURRENT_TIMESTAMP in database is in GMT, not the timezone of the machine That may be the problem in your case. you can retrieve the data and convert to your time zone and display to the user.

edited Dec 14 '11 at 06:29

answered Dec 14 '11 at 06:16

Sunil Kumar Sahoo

53,011
55
178
243

I used TIMESTAMP DEFAULT CURRENT_TIMESTAMP for the timestamp. My phone's time is fine, but the time stamp displays 4 hour advance – Kev Dec 14 '11 at 06:23
The default CURRENT_TIMESTAMP in database is in GMT, not the timezone of the machine – Sunil Kumar Sahoo Dec 14 '11 at 06:29
How should i fixed it then? Help me please, I'm just a beginner here in android. What should I do? – Kev Dec 14 '11 at 06:35

score 0 · Answer 3 · answered Dec 14 '11 at 07:20

StringBuffer sb=new StringBuffer();

    Calendar c = Calendar.getInstance();

    System.out.println("current: " + c.getTime());

    Log.i("GMT +", c.getTime().toString());
    // Log.i("GMT +", c.getTimeZone().toString());
    String gmt = c.getTime().toString();
    // String gmt = c.getTimeZone().toString();


    TimeZone z = c.getTimeZone();

    int offset = z.getRawOffset();
    int offsetHrs = offset / 1000 / 60 / 60;
    int offsetMins = offset / 1000 / 60 % 60;

    System.out.println("offset: hours " + offsetHrs);
    System.out.println("offset: min " + offsetMins);

    c.add(Calendar.HOUR_OF_DAY, (-offsetHrs));
    c.add(Calendar.MINUTE, (-offsetMins));

Android timestamp wrong

3 Answers3