วิธีใช้ unixepoch ใน SQLite บน Android

การใช้ unixepoch ใน SQLite บน Android เพื่อแปลง Timestamp

ฟังก์ชัน unixepoch ใน SQLite ใช้ร่วมกับ date(), time(), datetime(), strftime() เพื่อแปลง Unix Timestamp (วินาทีนับจาก 1970-01-01 UTC) ให้เป็นวันที่/เวลาที่อ่านได้

ขั้นตอนที่ 1: สร้างโปรเจกต์ใหม่

สร้างโปรเจกต์ Android ใหม่ใน Android Studio

ขั้นตอนที่ 2: ออกแบบ Layout (res/layout/activity_main.xml)

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="https://schemas.android.com/apk/res/android"
    xmlns:tools="https://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity"
    android:orientation="vertical"
    android:padding="16dp">

    <EditText
        android:id="@+id/etName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="ป้อนชื่อ" />

    <EditText
        android:id="@+id/etSalary"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="ป้อนเงินเดือน"
        android:inputType="numberDecimal" />

    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="16dp"
        android:orientation="horizontal">
        <Button android:id="@+id/btnSave" android:text="บันทึก" android:layout_width="wrap_content" android:layout_height="wrap_content" />
        <Button android:id="@+id/btnRefresh" android:text="รีเฟรช" android:layout_width="wrap_content" android/layout_height="wrap_content" />
        <Button android:id="@+id/btnUpdate" android:text="อัปเดต" android:layout_width="wrap_content" android:layout_height="wrap_content" />
        <Button android:id="@+id/btnDelete" android:text="ลบทั้งหมด" android:layout_width="wrap_content" android:layout_height="wrap_content" />
    </LinearLayout>

    <ListView
        android:id="@+id/listView"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="16dp">
    </ListView>
</LinearLayout>

ขั้นตอนที่ 3: MainActivity.java

package com.example.myapplication;

import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.Toast;
import androidx.appcompat.app.AppCompatActivity;
import java.util.ArrayList;

public class MainActivity extends AppCompatActivity {
    private EditText etName, etSalary;
    private ListView listView;
    private ArrayAdapter<String> adapter;
    private DatabaseHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbHelper = new DatabaseHelper(this);
        ArrayList<String> dataList = dbHelper.getAllContactsWithUnixEpoch();

        etName = findViewById(R.id.etName);
        etSalary = findViewById(R.id.etSalary);
        listView = findViewById(R.id.listView);

        adapter = new ArrayAdapter<>(this, android.R.layout.simple_list_item_1, dataList);
        listView.setAdapter(adapter);

        findViewById(R.id.btnDelete).setOnClickListener(v -> {
            Toast.makeText(this, dbHelper.deleteAll() ? "ลบสำเร็จ" : "ลบไม่สำเร็จ", Toast.LENGTH_LONG).show();
        });

        findViewById(R.id.btnUpdate).setOnClickListener(v -> {
            String name = etName.getText().toString().trim();
            String salary = etSalary.getText().toString().trim();
            if (!name.isEmpty() && !salary.isEmpty()) {
                Toast.makeText(this, dbHelper.update(name, salary) ? "อัปเดตสำเร็จ" : "อัปเดตไม่สำเร็จ", Toast.LENGTH_LONG).show();
            } else {
                etName.setError("กรุณาป้อนชื่อ");
                etSalary.setError("กรุณาป้อนเงินเดือน");
            }
        });

        findViewById(R.id.btnRefresh).setOnClickListener(v -> {
            dataList.clear();
            dataList.addAll(dbHelper.getAllContactsWithUnixEpoch());
            adapter.notifyDataSetChanged();
        });

        findViewById(R.id.btnSave).setOnClickListener(v -> {
            String name = etName.getText().toString().trim();
            String salary = etSalary.getText().toString().trim();
            if (!name.isEmpty() && !salary.isEmpty()) {
                Toast.makeText(this, dbHelper.insert(name, salary) ? "บันทึกสำเร็จ" : "บันทึกไม่สำเร็จ", Toast.LENGTH_LONG).show();
            } else {
                etName.setError("กรุณาป้อนชื่อ");
                etSalary.setError("กรุณาป้อนเงินเดือน");
            }
        });
    }
}

ขั้นตอนที่ 4: DatabaseHelper.java - ใช้ unixepoch ใน Query

package com.example.myapplication;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "salaryDatabase";
    private static final String TABLE_NAME = "SalaryDetails";
    private static final int DB_VERSION = 1;

    public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // salary ใช้ BLOB ในตัวอย่างเดิม แต่ควรเป็น REAL สำหรับตัวเลข
        String sql = "CREATE TABLE " + TABLE_NAME + " (id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                     "name TEXT, salary REAL, created_at INTEGER DEFAULT (strftime('%s', 'now')))";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    public boolean insert(String name, String salary) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("salary", salary);
        // created_at จะถูกเซ็ตอัตโนมัติด้วย DEFAULT (Unix timestamp)
        return db.insert(TABLE_NAME, null, values) != -1;
    }

    // จุดสำคัญ: ใช้ unixepoch แปลง timestamp
    public ArrayList<String> getAllContactsWithUnixEpoch() {
        SQLiteDatabase db = getReadableDatabase();
        ArrayList<String> list = new ArrayList<>();
        
        // strftime('%s', 'now') = Unix timestamp ปัจจุบัน
        // datetime(..., 'unixepoch', 'localtime') = แปลงเป็น local time
        String query = "SELECT id, name, salary, " +
                       "datetime(created_at, 'unixepoch', 'localtime') as local_time " +
                       "FROM " + TABLE_NAME;
        
        Cursor cursor = db.rawQuery(query, null);

        if (cursor.moveToFirst()) {
            do {
                String row = cursor.getInt(0) + " : " + cursor.getString(1) + " : " + 
                             cursor.getDouble(2) + " : " + cursor.getString(3);
                list.add(row);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

    public boolean update(String name, String salary) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("salary", salary);
        return db.update(TABLE_NAME, values, "name = ?", new String[]{name}) > 0;
    }

    public boolean deleteAll() {
        return getWritableDatabase().delete(TABLE_NAME, null, null) > 0;
    }
}

คำอธิบาย unixepoch และ Date/Time Functions

1. Unix Timestamp คืออะไร?

• จำนวนวินาทีนับจาก 1970-01-01 00:00:00 UTC (Unix Epoch)
• ตัวอย่าง: 1700000000 = 2023-11-14 13:46:40 UTC
• เก็บเป็น INTEGER ใน SQLite (ขนาดเล็ก, คำนวณง่าย, Timezone independent)

2. Modifier 'unixepoch'

datetime(unix_timestamp, 'unixepoch')           -- UTC time
datetime(unix_timestamp, 'unixepoch', 'localtime') -- Local time (ตาม Timezone ของ device)
date(unix_timestamp, 'unixepoch')               -- Date only (YYYY-MM-DD)
time(unix_timestamp, 'unixepoch')               -- Time only (HH:MM:SS)
strftime('%Y-%m-%d %H:%M', unix_timestamp, 'unixepoch', 'localtime') -- Custom format

3. Modifier อื่นๆ ที่ใช้ร่วมได้

ตัวอย่าง Query ต่างๆ

1. แปลง Timestamp ในตารางเป็น Local Time

SELECT datetime(created_at, 'unixepoch', 'localtime') FROM events;

2. หาข้อมูล 7 วันที่ผ่านมา

SELECT * FROM events 
WHERE created_at >= strftime('%s', 'now', '-7 days', 'unixepoch');

3. จัดกลุ่มตามวัน (Local Date)

SELECT date(created_at, 'unixepoch', 'localtime') as day, COUNT(*) 
FROM events GROUP BY day;

4. แปลงเป็น Thai Buddhist Era (พ.ศ.)

SELECT strftime('%d/%m/%Y', created_at, 'unixepoch', 'localtime') as thai_date
FROM events;
-- แล้วบวก 543 กับปีใน Application Layer

ตัวอย่างผลลัพธ์

1 : Sairam : 25000.50 : 2024-01-15 14:30:25
2 : John   : 30000.00 : 2024-01-15 14:31:10
3 : Jane   : 28000.75 : 2024-01-15 14:32:05

Best Practices สำหรับ Date/Time ใน SQLite

• เก็บเป็น Unix Timestamp (INTEGER) - มาตรฐาน, ขนาดเล็ก, ไม่มี Timezone Issue
• ใช้ DEFAULT (strftime('%s', 'now')) - เซ็ตเวลาอัตโนมัติตอน INSERT
• แปลงที่ Query Time - ใช้ unixepoch, localtime ตอน SELECT
• Index บน Timestamp Column - เร็วสำหรับ Range Query (WHERE created_at BETWEEN ...)
• หลีกเลี่ยง TEXT Date Strings - ยากต่อการคำนวณ/เรียง/เปรียบเทียบ

Java/Kotlin Side - การจัดการ Timestamp

// Insert: Unix timestamp ปัจจุบัน (seconds)
long now = System.currentTimeMillis() / 1000;
values.put("created_at", now);

// Query: แปลงเป็น Date Object
long timestamp = cursor.getLong(cursor.getColumnIndex("created_at"));
Date date = new Date(timestamp * 1000); // Java Date ใช้ milliseconds
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault());
String formatted = sdf.format(date);

// หรือใช้ java.time (API 26+)
Instant instant = Instant.ofEpochSecond(timestamp);
ZonedDateTime zdt = instant.atZone(ZoneId.systemDefault());
DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
String formatted = zdt.format(fmt);

ข้อควรระวัง

• SQLite Date Functions ใช้ UTC เป็นฐาน - ต้องระบุ 'localtime' เพื่อแปลง
• 'now' ใน SQLite = เวลาของ SQLite Engine (อาจต่างจาก System Time เล็กน้อย)
• Year 0000-9999 เท่านั้น - นอกเหนือจากนี้จะคืน NULL
• Daylight Saving Time - 'localtime' จัดการอัตโนมัติตาม TZ Database