Learning Focus

Use this lesson to understand Timestamp with practical syntax and examples.

MySQL TIMESTAMP Data Type: Complete Guide

---

What is TIMESTAMP?

TIMESTAMP is a MySQL data type that stores date and time with timezone conversion. It automatically converts values to Coordinated Universal Time (UTC) for storage and back to the current timezone for retrieval.

---

Key Features

Feature	Details
Format	YYYY-MM-DD HH:MM:SS (e.g., 2023-10-15 14:30:45)
Range	1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC
Storage	4 bytes
Timezone	Converts to/from UTC
Auto-Set	Can auto-initialize/update to current timestamp

---

Declaration & Usage

Basic Syntax

column_name TIMESTAMP [DEFAULT CURRENT_TIMESTAMP] [ON UPDATE CURRENT_TIMESTAMP]

Example Table

CREATE TABLE logs (
    id INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Insert/Update Behavior

• Insert:

  INSERT INTO logs (id) VALUES (1);
  -- created_at and updated_at auto-set to current time
  

• Update:

  UPDATE logs SET id = 2 WHERE id = 1;
  -- updated_at auto-changes to current time
  

---

Timezone Handling

• Stored as UTC regardless of server timezone.

• Retrieved in current timezone:

  SET time_zone = '+00:00';  -- UTC
  SELECT created_at FROM logs;  -- Shows UTC time
  
  SET time_zone = '+08:00';  -- Singapore time
  SELECT created_at FROM logs;  -- Converts to +08:00
  

Key System Variables

SHOW VARIABLES LIKE '%time_zone%';
-- time_zone: SYSTEM (default)
-- system_time_zone: Server OS timezone

---

TIMESTAMP vs DATETIME

Feature	TIMESTAMP	DATETIME
Range	1970-2038	1000-9999
Storage	4 bytes	5 bytes
Timezone	UTC-converted	Timezone-neutral
Auto-Set	Supported	Not supported
Portability	Affected by time_zone	Fixed value

---

Common Functions

Get Current TIMESTAMP

SELECT CURRENT_TIMESTAMP();  -- Returns server time in session timezone

Convert to Unix Time

SELECT UNIX_TIMESTAMP(created_at) FROM logs;  -- Seconds since 1970-01-01 UTC

Convert from Unix Time

SELECT FROM_UNIXTIME(1697383445);  -- 2023-10-15 14:30:45 (local timezone)

---

Best Practices

1. Use for audit columns:

   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   

2. Set timezone explicitly:

   SET time_zone = '+00:00';  -- Standardize on UTC
   

3. Avoid for historical/future dates: Use DATETIME for dates before 1970 or after 2038.

4. Store in UTC: Convert application times to UTC before storage.

---

Example Use Cases

User Activity Tracking

CREATE TABLE user_activity (
    user_id INT,
    action VARCHAR(50),
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Session Expiration

CREATE TABLE sessions (
    session_id VARCHAR(100) PRIMARY KEY,
    expires_at TIMESTAMP  -- UTC time
);

---

Common Issues

1. Year 2038 Problem

• TIMESTAMP cannot store dates beyond 2038-01-19 03:14:07 UTC.
• Solution: Use DATETIME for long-term dates.

2. Timezone Mismatch

-- Server in UTC, client in PST:
SET time_zone = '+00:00'; INSERT INTO logs (id) VALUES (1);
SET time_zone = '-08:00'; SELECT created_at;  -- Shows adjusted time

3. Silent Truncation

INSERT INTO logs (created_at) VALUES ('2039-01-01 00:00:00');  -- Error!

---

Summary

• TIMESTAMP stores UTC date/time with auto-conversion.
• Best for: Audit columns, timezone-aware apps.
• Avoid for: Dates outside 1970-2038.

---

Next Topics

1. Time Zone Management
2. Date/Time Functions
3. Migrating from TIMESTAMP to DATETIME

Concept Map

flowchart LR
    A[Schema Context] --> B[Timestamp]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

CREATE TABLE sample_timestamp (id INT PRIMARY KEY, value TIMESTAMP);
DESCRIBE sample_table;
SHOW CREATE TABLE sample_table;

What's Next

• Previous: YEAR - Review the previous lesson to reinforce context.
• Module Overview - Return to this module index and choose another related lesson.