Unix Timestamp Development Best Practices

Authoritative guide and practical experience for timestamp handling in enterprise applications

Enterprise-GradeProduction ReadyPerformance OptimizedSecurity Focused

Database Design Best Practices

Key strategies for correctly storing and processing timestamps in databases

Storage Strategy Selection

✅ Recommended Practices

• Use BIGINT to store Unix timestamps
• Consistently use UTC time
• Millisecond precision meets most requirements
• Create indexes for time fields

❌ Practices to Avoid

• Using VARCHAR to store timestamps
• Mixing different timezones
• Overly precise nanosecond storage
• Ignoring index optimization

Standard Table Schema Design

-- Recommended table schema design
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    
    -- Timestamp fields (millisecond precision)
    created_at BIGINT NOT NULL,
    updated_at BIGINT NOT NULL,
    last_login_at BIGINT NULL,
    deleted_at BIGINT NULL,
    
    -- Create indexes for time fields
    INDEX idx_created_at (created_at),
    INDEX idx_updated_at (updated_at),
    INDEX idx_last_login (last_login_at),
    INDEX idx_deleted_at (deleted_at)
);

-- Trigger to automatically update timestamps
DELIMITER $$
CREATE TRIGGER users_updated_at 
    BEFORE UPDATE ON users
    FOR EACH ROW
BEGIN
    SET NEW.updated_at = UNIX_TIMESTAMP(NOW(3)) * 1000;
END$$
DELIMITER ;

Query Optimization Techniques

Time Range Queries

-- Query data from the last 24 hours
SELECT * FROM users 
WHERE created_at >= (UNIX_TIMESTAMP(NOW()) - 86400) * 1000;

-- Query specific date range
SELECT * FROM users 
WHERE created_at BETWEEN 1703980800000 AND 1704067199999;

Pagination Query Optimization

-- Use timestamp for cursor-based pagination
SELECT * FROM users 
WHERE created_at > :last_timestamp 
ORDER BY created_at ASC 
LIMIT 20;

API Design Best Practices

Key principles for building time-friendly RESTful APIs

Response Format Standardization

{
  "data": {
    "id": 12345,
    "title": "Example Article",
    "content": "Article content...",
    
    // Provide both timestamp and ISO format
    "created_at": 1703123456789,
    "created_at_iso": "2023-12-21T01:23:45.678Z",
    "updated_at": 1703123456789,
    "updated_at_iso": "2023-12-21T01:23:45.678Z"
  },
  "meta": {
    "timezone": "UTC",
    "timestamp_precision": "milliseconds",
    "server_time": 1703123456789
  }
}

Input Validation and Error Handling

Timestamp Validation

// JavaScript validation example
function validateTimestamp(timestamp) {
  // Check if it's a number
  if (!Number.isInteger(timestamp)) {
    throw new Error('Timestamp must be an integer');
  }
  
  // Check reasonable range (1970-2100)
  const min = 0;
  const max = 4102444800000; // Year 2100
  
  if (timestamp < min || timestamp > max) {
    throw new Error('Timestamp out of valid range');
  }
  
  return true;
}

Error Response Format

{
  "error": {
    "code": "INVALID_TIMESTAMP",
    "message": "The provided timestamp is invalid",
    "details": {
      "field": "created_at",
      "value": "invalid_value",
      "expected": "Unix timestamp in milliseconds"
    }
  },
  "timestamp": 1703123456789
}

Query Parameter Design

Time Range Queries

Flexible query parameters supporting multiple time formats

GET /api/posts?start_time=1703123456&end_time=1703209856
GET /api/posts?start_time=2023-12-21&end_time=2023-12-22
GET /api/posts?since=24h&until=now

Security Best Practices

Security strategies to protect time-related functionality from attacks

Preventing Time-Related Attacks

Timestamp Injection Attacks

Malicious users may attempt to inject abnormal timestamp values to bypass business logic or cause system errors.

// Protection measures
function sanitizeTimestamp(input) {
  // Strict type checking
  const timestamp = parseInt(input, 10);
  
  // Range validation
  const now = Date.now();
  const oneYearAgo = now - (365 * 24 * 60 * 60 * 1000);
  const oneYearLater = now + (365 * 24 * 60 * 60 * 1000);
  
  if (timestamp < oneYearAgo || timestamp > oneYearLater) {
    throw new Error('Timestamp out of allowed range');
  }
  
  return timestamp;
}

Timing Attack Protection

Prevent attackers from inferring system internal state by analyzing response times.

// Constant time comparison
function constantTimeCompare(a, b) {
  if (a.length !== b.length) {
    return false;
  }
  
  let result = 0;
  for (let i = 0; i < a.length; i++) {
    result |= a.charCodeAt(i) ^ b.charCodeAt(i);
  }
  
  return result === 0;
}

Access Control and Auditing

Operation Log Recording

// Audit log structure
{
  "event_id": "evt_123456",
  "user_id": "user_789",
  "action": "timestamp_conversion",
  "resource": "/api/convert",
  "timestamp": 1703123456789,
  "ip_address": "192.168.1.100",
  "user_agent": "Mozilla/5.0...",
  "request_data": {
    "input_timestamp": 1703123456,
    "output_format": "iso"
  },
  "response_status": 200,
  "processing_time_ms": 45
}

Performance Optimization Strategies

Performance optimization techniques for timestamp handling in large-scale applications

Caching Strategies

Timezone Conversion Cache

// Redis cache timezone conversion results
const cacheKey = `tz:${timestamp}:${timezone}`;
const cached = await redis.get(cacheKey);

if (cached) {
  return JSON.parse(cached);
}

const converted = convertTimezone(timestamp, timezone);
await redis.setex(cacheKey, 3600, JSON.stringify(converted));

return converted;

Format Result Cache

// In-memory cache common formats
const formatCache = new Map();

function formatTimestamp(timestamp, format) {
  const key = `${timestamp}:${format}`;
  
  if (formatCache.has(key)) {
    return formatCache.get(key);
  }
  
  const result = doFormat(timestamp, format);
  formatCache.set(key, result);
  
  return result;
}

Batch Processing Optimization

// Batch timestamp conversion
async function batchConvertTimestamps(timestamps, options = {}) {
  const { batchSize = 1000, format = 'iso' } = options;
  const results = [];
  
  // Process in batches to avoid memory overflow
  for (let i = 0; i < timestamps.length; i += batchSize) {
    const batch = timestamps.slice(i, i + batchSize);
    
    // Parallel processing within each batch
    const batchResults = await Promise.all(
      batch.map(async (timestamp) => {
        try {
          return {
            input: timestamp,
            output: await convertTimestamp(timestamp, format),
            success: true
          };
        } catch (error) {
          return {
            input: timestamp,
            error: error.message,
            success: false
          };
        }
      })
    );
    
    results.push(...batchResults);
    
    // Avoid blocking the event loop
    await new Promise(resolve => setImmediate(resolve));
  }
  
  return results;
}

Team Collaboration Guidelines

Ensure team consistency and collaboration efficiency in timestamp handling

Code Standards

Naming Conventions

• Use _at suffix consistently for timestamp fields
• Function names clearly indicate input and output formats
• Constants use the TIMESTAMP_ prefix
• Timezone-related variables include timezone or tz

Documentation Requirements

• Clearly indicate timestamp precision (seconds/milliseconds)
• Explain timezone handling approach
• Provide usage examples
• Document known limitations and caveats

Testing Guidelines

// Timestamp-related testing examples
describe('Timestamp Conversion', () => {
  // Test boundary values
  test('handles Unix epoch', () => {
    expect(convertTimestamp(0)).toBe('1970-01-01T00:00:00.000Z');
  });
  
  // Test precision
  test('preserves millisecond precision', () => {
    const timestamp = 1703123456789;
    const result = convertTimestamp(timestamp);
    expect(result).toContain('.789Z');
  });
  
  // Test error handling
  test('throws error for invalid timestamp', () => {
    expect(() => convertTimestamp('invalid')).toThrow();
    expect(() => convertTimestamp(-1)).toThrow();
  });
  
  // Test timezone handling
  test('handles timezone conversion', () => {
    const timestamp = 1703123456000;
    const utc = convertTimestamp(timestamp, 'UTC');
    const est = convertTimestamp(timestamp, 'America/New_York');
    expect(utc).not.toBe(est);
  });
});

Real-World Case Studies

Practical scenarios to illustrate how the best practices reduce bugs and improve reliability

Case 1: Billing Cutoff at Month End

A SaaS system had off‑by‑one‑hour invoices for users in DST regions. Root cause: local time math with DST boundary.

Store UTC timestamps for invoices
Do reporting math in UTC
Convert to user timezone only for display

Case 2: Analytics Sampling Windows

Product analytics showed inconsistent day buckets across services.

Standardize on ISO 8601 + Unix ms in events
Define day boundaries in UTC
Use server‑side authoritative clock

Case 3: Database Hot Partitions

Append‑only writes on the same second created hot keys.

Use millisecond precision keys
Apply sharding by time bucket
Backfill with batches and throttling

Case 4: Mobile Offline Events

Out‑of‑order events after long offline sessions.

Send both device_time and server_received_at
Order by server time with tolerance
Deduplicate by (user, device_ts, hash)

Recommended Tools and Resources

Developer Tools

Online Timestamp Converter
API Testing Tools (Postman/Insomnia)
Database Management Tools
Log Analysis Tools

Learning Resources

Complete Guide to Timestamps
API Design Documentation
Performance Optimization Case Studies
Security Best Practices

View Full Guide Get Started

These best practices are based on real production experience to help you build more reliable and secure timestamp handling systems.

If you have additional best practices to share, we’d love to hear from you.