Dates

functional

Read dates your way: pick a pattern and time zone, or turn epoch numbers into real dates.

Dates become much easier to trust when their format and time zone are clear. Set both in Settings › Results › Date.

OptionChoicesDefault
Format patternFree-text pattern (yyyy, MM, dd, HH, mm, ss)yyyy-MM-dd HH:mm:ss
Time zoneAlways UTC / Native (as stored) / Always local / Specific time zoneAlways UTC
Time zone idSearchable IANA zone picker, shown when “Specific time zone” is chosen - the zone is validated as you type and a live preview shows its current UTC offsetUTC
AlignmentLeft / Center / RightLeft
Show integer timestamps as datesOff / By column name / By name and value rangeOff

Dates use the pattern you choose; there is no relative “2 hours ago” mode. To turn an integer epoch into a date, use an epoch= format override.

SQLly
SQLly date formatting configuration panelSQLly date formatting configuration panel
The date/time formatting panel in Settings.

Writing a pattern

The pattern recognizes six tokens: yyyy (four-digit year), MM (month), dd (day), HH (24-hour hour), mm (minute), and ss (second). Everything else - dashes, slashes, dots, spaces, a literal T - is copied as typed. For a value of 2026-03-14 09:05:07:

PatternGrid shows
yyyy-MM-dd HH:mm:ss2026-03-14 09:05:07
dd/MM/yyyy HH:mm14/03/2026 09:05
MM.dd.yyyy03.14.2026
yyyy-MM-ddTHH:mm:ss2026-03-14T09:05:07

Month names, two-digit years, and a 12-hour clock are not tokens. Fractional seconds are never thrown away: when a value carries them and your pattern includes ss, they are appended, so 12:00:00.500 stays 12:00:00.500.

Time zones

Take a timestamptz value of 2026-01-01T12:00:00+00:00 with the default pattern:

Time zone settingGrid shows
Always UTC2026-01-01 12:00:00
Specific time zone: Europe/Berlin2026-01-01 13:00:00
Specific time zone: Asia/Tokyo2026-01-01 21:00:00
Always localThe same instant in your computer's time zone
Native (as stored)The text exactly as the database sent it
  • Values with an offset (timestamptz, datetimeoffset) convert exactly, the instant is the same, only the clock reading changes.
  • Values without an offset (datetime, datetime2, timestamp) are treated as UTC before converting. If your database stores local wall-clock times in those columns, pick Always UTC or Native (as stored) so they are not shifted.
  • Native (as stored) skips the pattern too: you see the engine's own text, untouched.
  • An unknown zone id is flagged in Settings and the grid falls back to UTC. In the browser preview, Always local also shows UTC.

Zones per column and per connection

The setting above is the default. Two more places can choose a zone, and the most specific one wins:

  1. A zone= option on the query's format directive.
  2. A column override. Date and Epoch overrides in Settings › Results › Column overrides have a Time zone choice: Follow global setting (the default for new overrides), Always UTC, Always local, Native (as stored, Date only), or a specific time zone picked from the same searchable list.
  3. The connection. In the connection editor, Display time zone can be Use app setting (the default), UTC, Local time, or any named zone. Grids from that connection then use it wherever this page's setting would apply, handy when one server keeps local wall-clock times and another keeps UTC.
  4. This page's Time zone setting.

What is left alone

  • Date-only and time-only columns (date, time) are shown as the database sent them, never shifted or re-patterned by the Settings pattern, moving a bare date across zones would change the day. A date= override (below) can still re-pattern a date-only column.
  • Text columns that happen to hold dates stay text. Formatting follows the declared column type; CAST the column to a date/time type in your query, or give the column a date= override, if you want it treated as one.
  • NULL shows as an italic NULL.

One column at a time

Right-click a date or time column's header and choose Format › Set Formatting Override… to give just that column its own pattern. The choice is saved into the query as a date= directive, which uses strftime tokens such as %Y, %m, %d, %H, %M, %S, %b (short month name), and %:z (UTC offset). For a datetimeoffset value of 2026-07-14T09:30:15+02:00 with the default Always UTC setting:

ChoiceDirectiveGrid shows
Date (2026-07-14)date=%Y-%m-%d2026-07-14
Date and timedate=%Y-%m-%d %H:%M:%S2026-07-14 07:30:15
Date, time, and offsetdate=%Y-%m-%d %H:%M:%S %:z2026-07-14 07:30:15 +00:00
Typed by handdate=%d/%m/%Y %H:%M14/07/2026 07:30
  • The time zone setting still applies. The directive picks the pattern; the instant is shown in the column's display time zone, as above. Under Native (as stored) a value with an offset keeps its own, so the offset choice shows 2026-07-14 09:30:15 +02:00.
  • Date-only values are re-patterned too (date=%d.%m.%Y turns 2026-07-14 into 14.07.2026) but never shifted across zones.
  • Mistakes are harmless. A value that does not read as a date, or a pattern with a token SQLly does not know, leaves the cell as the database sent it.
  • Display only. Copy and export carry the database's own value, not the re-patterned text.
-- sqlly format: column=OrderDate; date=%d %b %Y
SELECT OrderID, OrderDate FROM dbo.Orders;

Epoch numbers

Unix timestamps stored as integers can be shown as dates per column. Right-click the header, choose Format › Set Formatting Override…, and pick Epoch seconds → date/time or Epoch milliseconds → date/time, or write it yourself:

-- sqlly format: column=created_at; epoch=auto
SELECT id, created_at FROM events;

With that line, 1767268800 shows as 2026-01-01 12:00:00 (UTC). auto treats values of 100,000,000,000 and above as milliseconds and smaller ones as seconds. A value that would land outside the years 1-9999 - usually a sign of the wrong unit, is left as the plain number. For a different output pattern, add an Epoch override for the column in Settings › Results › Column overrides, where you can choose the unit and pick a datetime pattern and time zone.

Epoch columns render in the same display time zone as dates (above), not always UTC. To skip the per-column setup, set Show integer timestamps as dates: By column name shows integer columns named like created_at, updated_ts, login_time, signup_epoch, or timestamp as dates (durations such as response_time or elapsed_time are left alone); By name and value range also leaves any value outside 2000-2100 (as seconds or milliseconds) as the plain number. A column override or format directive always wins, and copy, edit, and export keep the raw number.