Date and Time

Dates & times

Storing "when something happened" correctly so it's consistent and easy to query.

Use a real date/time column, not a number-of-seconds in text

MySQL has proper temporal types — use them:

Type	Range / use
DATETIME	a date+time, no timezone math (e.g. 2025-01-01 12:00:00)
TIMESTAMP	like DATETIME but auto-converts to/from UTC; good for "created at"
DATE	just a date (2025-01-01)
TIME	just a time of day

For most gamemode needs (created, last_login, banned_until), DATETIME is the simple choice.

Let MySQL stamp the time

Don't compute timestamps in Pawn — let the database do it so it's consistent:

CREATE TABLE accounts (
    ...
    created    DATETIME DEFAULT CURRENT_TIMESTAMP,   -- auto-set on insert
    last_login DATETIME
);

-- on each login:
UPDATE accounts SET last_login = NOW() WHERE id = ?;

NOW() and CURRENT_TIMESTAMP are the current server time.

Reading a date/time in Pawn

Read it as a string — there's no native date type in Pawn:

new lastLogin[20];
mysql_rs_get_string_by(0, "last_login", lastLogin, sizeof lastLogin);
// "2025-01-01 12:00:00"

Useful date math (let SQL do it)

-- accounts created in the last 7 days
SELECT COUNT(*) FROM accounts WHERE created >= NOW() - INTERVAL 7 DAY;

-- how long ago, in seconds
SELECT TIMESTAMPDIFF(SECOND, last_login, NOW()) AS ago FROM accounts WHERE id = ?;

-- a temp ban that expires
UPDATE accounts SET banned_until = NOW() + INTERVAL 3 DAY WHERE id = ?;
-- ...later, is the ban still active?
SELECT (banned_until > NOW()) AS still_banned FROM accounts WHERE id = ?;

Playtime: store seconds as an INT

"Total seconds played" is a number, not a time-of-day — store it as INT and add to it on save:

playtime INT NOT NULL DEFAULT 0   -- seconds

UPDATE accounts SET playtime = playtime + ? WHERE id = ?;

Format it for display in Pawn (hours = playtime / 3600, etc.).

Timezones (only if you care)

TIMESTAMP stores in UTC and converts using the server's timezone; DATETIME stores the literal value you give it. For a single server, either is fine — just be consistent. If you show times to players in different regions, store UTC and convert for display.

Next: Storing game data · SQL crash course