Date and time functions

Take a time apart, find the weekday, quarter and week, cut, add and build times, count days or months between times, fiscal years (also Nepal's), and write a time as text.

• Category: Functions — date and time
• Keywords: date, time, timestamp, UTC, milliseconds, weekday, weekday, trading day, holiday, truncate time, timestamp_floor, timestamp floor, local midnight, start of month, start of week, add days, add months, date_add, timestampadd, interval, month end, difference between dates, age, date_diff, timestampdiff, format date, strftime, date_format, format, time zone, timezone, Nepal time, NPT, Asia/Kathmandu, +05:45, 20700000, current_timestamp, getdate, EXTRACT, datepart, date part, epoch, ISO week, week number, weekofyear, dayofyear, day of year, quarter of the year, end of month, EOMONTH, last day of month, build a date, date from parts, datefromparts, how old, time between, financial year, FY, fiscal year, Nepal fiscal year, Nepali fiscal year, Shrawan, Bikram Sambat, BS year, arthik barsa, to_timezone, to_utc, AT TIME ZONE, timestamp_ceil, round up time, timestamp_floor_utc, today, yesterday, tomorrow, interval_start, interval_end, days_in_month, is_leap_year, leap year, is_end_of_month, day_of_week_sunday_first, millis, to_pg_date, sysdate, timestamp_sequence, timestamp_shuffle, QuestDB
• See also: Conversion functions (to_timestamp), Conversion functions (cast), Time and time zones, SAMPLE BY, SAMPLE BY (time-zone), WHERE (in-interval), Aggregate functions, Finance and price functions

KDB keeps a TIMESTAMP as the number of milliseconds (1/1000 of a second) since 1970-01-01, in UTC. UTC is the world clock time with no time zone. A local time is UTC plus its zone offset: Nepal is +05:45 (11:00 in Kathmandu is 05:15 UTC), New York is -04:00 in summer (07:00 there is 11:00 UTC).

• Every function here works on the session's time zone, which is UTC unless the session ran SET TIME ZONE or the server has --timezone. A time zone given in the call wins: date_trunc(unit, t, zone), timestamp_floor, to_timezone and to_utc, to_char(t, format, zone). hour() of 11:00 local time in Nepal is 5. See Local time.
• A time argument can be a TIMESTAMP, a DATE, time text like '2026-09-10T11:00:00+05:45', or a whole number (milliseconds since 1970).
• A NULL argument gives NULL.
• Text that is not a time is an error: year('hello').
• to_timestamp and to_date change a value into a time: see conversion functions.
• For reports: date_part takes any part of a time, quarter and week_of_year group by quarter and week, last_day finds the month end, and fiscal_year and nepal_fiscal_year group by financial year.

Example: CITY volume per Nepal clock hour (sample data: sample)

SELECT hour(time + 20700000) AS nepal_hour,
       count(*) AS candles,
       sum(volume) AS volume
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10'
GROUP BY hour(time + 20700000)
ORDER BY nepal_hour;

Result:

(4 rows)

now, systimestamp

Syntax:

now()
systimestamp()

The current time. systimestamp is the same function.

• Returns: TIMESTAMP (UTC).
• NULL: never NULL.
• Notes: the value is the same for the whole statement, so every row of one SELECT or INSERT gets the same time. As a column DEFAULT now(), a new row gets the time it was inserted. The PostgreSQL names CURRENT_TIMESTAMP, current_date, localtimestamp, transaction_timestamp, statement_timestamp and clock_timestamp give the same time, and so does QuestDB's sysdate.

Example

SELECT now() > '2026-01-01' AS after_2026, now() = systimestamp() AS same_time, year(now()) >= 2026 AS year_ok;

Result:

(1 row)

Example: Save the login time of an app user

Setup:

CREATE TABLE logins (user_id INT, logged_in_at TIMESTAMP DEFAULT now());
INSERT INTO logins (user_id) VALUES (7);

SELECT user_id, logged_in_at > '2026-01-01' AS has_time FROM logins;

Result:

(1 row)

Example: The PostgreSQL names give the same time

SELECT now() = current_timestamp AS same_1, now() = current_timestamp() AS same_2, now() = localtimestamp AS same_3;

Result:

(1 row)

Example: getdate is not KDB SQL (shows an error)

SELECT getdate();

Result:

Error: line 1, column 8: unknown function 'getdate'

year

Syntax:

year(t)

The year of the UTC date: year('2026-09-10') is 2026.

• Returns: LONG.
• NULL: year(NULL) is NULL.
• Notes: the date is the date on the session's clock, UTC unless set. In a UTC session a time just after midnight in Nepal (before 05:45) still belongs to the day before; on 1 January that is even the year before.

Example

SELECT year('2026-09-10T08:30:15Z') AS a, year(CAST('2025-12-31' AS DATE)) AS b, year('2026-01-01T02:00:00+05:45') AS nepal_new_year, year(NULL) AS c;

Result:

(1 row)

Example: Average CITY close per year (sample data: sample)

SELECT year(time) AS year, round(avg(close), 2) AS avg_close, count(*) AS days
FROM ochl_day
WHERE symbol = 'CITY'
GROUP BY year(time)
ORDER BY year;

Result:

(2 rows)

Example: Text that is not a full date (shows an error)

SELECT year('2026-09');

Result:

Error: line 1, column 8: year: argument 1 must be a time, got '2026-09'

month

Syntax:

month(t)

The month of the UTC date, from 1 (January) to 12 (December).

• Returns: LONG.
• NULL: month(NULL) is NULL.
• Notes: for the month name use to_char with Mon or Month. For the first moment of the month use date_trunc('month', t).

Example

SELECT month('2026-09-10') AS a, month(CAST('2025-12-28' AS DATE)) AS b;

Result:

(1 row)

Example: Month report: trading days and volume per symbol (sample data: sample)

SELECT year(time) AS year, month(time) AS month, symbol,
       count(*) AS days, sum(volume) AS volume
FROM ochl_day
WHERE symbol IN ('CITY', 'VOLT')
GROUP BY year(time), month(time), symbol
ORDER BY year, month, symbol;

Result:

(6 rows)

day

Syntax:

day(t)

The day of the month of the UTC date, from 1 to 31.

• Returns: LONG.
• NULL: day(NULL) is NULL.
• Notes: the UTC day and the Nepal day differ between 00:00 and 05:45 Nepal time. Add 20700000 ms (5 h 45 min) to get the Nepal day.

Example

SELECT day('2026-09-10T20:00:00Z') AS a, day('2026-09-02T02:30:00+05:45') AS utc_day, day(CAST('2026-09-02T02:30:00+05:45' AS TIMESTAMP) + 20700000) AS nepal_day;

Result:

(1 row)

Example: App sign-ups per day: the UTC day and the Nepal day (sample data: fn-dates)

SELECT day(created_at) AS utc_day, day(created_at + 20700000) AS nepal_day, count(*) AS signups
FROM signups
GROUP BY day(created_at), day(created_at + 20700000)
ORDER BY nepal_day, utc_day;

Result:

(6 rows)

hour

Syntax:

hour(t)

The hour of the UTC time, from 0 to 23.

• Returns: LONG.
• NULL: hour(NULL) is NULL.
• Notes: UTC. hour('2026-09-10T11:00:00+05:45') is 5, not 11. Use hour(time + 20700000) for the Nepal clock hour (20700000 ms is 5 h 45 min; this is always right because Nepal has no summer time). A DATE has hour 0. Text without a zone is read as UTC.

Example

SELECT hour('2026-09-10 11:00:00') AS no_zone_is_utc, hour('2026-09-10T11:00:00+05:45') AS nepal_11_in_utc, hour(CAST('2026-09-10T11:00:00+05:45' AS TIMESTAMP) + 20700000) AS nepal_hour;

Result:

(1 row)

Example: Forum posts per Nepal hour of the day (sample data: fn-dates)

SELECT hour(created_at + 20700000) AS nepal_hour, count(*) AS posts
FROM forum_posts
GROUP BY hour(created_at + 20700000)
ORDER BY posts DESC, nepal_hour
LIMIT 5;

Result:

(5 rows)

minute

Syntax:

minute(t)

The minute of the UTC time, from 0 to 59.

• Returns: LONG.
• NULL: minute(NULL) is NULL.
• Notes: Nepal is 45 minutes off from a whole hour, so the minute changes too: 11:07 in Nepal is 05:22 UTC.

Example

SELECT minute('2026-09-10T11:07:30+05:45') AS utc_minute, minute(CAST('2026-09-10T11:07:30+05:45' AS TIMESTAMP) + 20700000) AS nepal_minute;

Result:

(1 row)

Example: COAST candles in the first 5 minutes of the day (Nepal 11:00-11:04) (sample data: sample)

SELECT time, open, close
FROM ochl_minutes
WHERE symbol = 'COAST' AND time IN '2026-09-10'
  AND hour(time + 20700000) = 11 AND minute(time + 20700000) < 5;

Result:

(5 rows)

second

Syntax:

second(t)

The second of the UTC time, from 0 to 59. Milliseconds are not included.

• Returns: LONG.
• NULL: second(NULL) is NULL.
• Notes: for the milliseconds use to_char with MS.

Example

SELECT second('2026-09-10T11:00:59.999Z') AS a, second('2026-09-10T11:00:05+05:45') AS b;

Result:

(1 row)

day_of_week

Syntax:

day_of_week(t)

The weekday of the UTC date as a number: 0 = Sunday, 1 = Monday, ... 6 = Saturday.

• Returns: LONG.
• NULL: day_of_week(NULL) is NULL.
• Notes: Sunday is 0 (like PostgreSQL EXTRACT(DOW ...)). For the name use to_char with Dy or Day. to_char(t, 'D') counts from 1 = Sunday instead.

Example

SELECT day_of_week('2026-09-06') AS sunday, day_of_week('2026-09-10') AS thursday, day_of_week(CAST('2026-09-12' AS DATE)) AS saturday;

Result:

(1 row)

Example: Trading days per weekday (a Sunday-to-Thursday week) (sample data: sample)

SELECT day_of_week(time) AS dow, count(*) AS candles, round(avg(volume)) AS avg_volume
FROM ochl_day
GROUP BY day_of_week(time)
ORDER BY dow;

Result:

(5 rows)

is_trading_day

Syntax:

is_trading_day(t)

true when the UTC date is a Sunday, Monday, Tuesday, Wednesday or Thursday: a Sunday-to-Thursday trading week, as on some exchanges. false on Friday and Saturday.

• Returns: BOOL.
• NULL: is_trading_day(NULL) is NULL.
• Notes: this one-argument form knows no holidays and one fixed week. For a market's own weekdays (also a week that changed, like NEPSE's in April 2026) and its holidays, make a trading calendar and write is_trading_day('nepse', t); next_trading_day, prev_trading_day and trading_days_between come with it.

Example

SELECT is_trading_day('2026-09-10') AS thursday, is_trading_day('2026-09-11') AS friday, is_trading_day('2026-09-12') AS saturday, is_trading_day('2026-09-13') AS sunday;

Result:

(1 row)

Example: App sign-ups on trading days and on the weekend (Nepal date) (sample data: fn-dates)

SELECT is_trading_day(created_at + 20700000) AS trading_day, count(*) AS signups
FROM signups
GROUP BY is_trading_day(created_at + 20700000)
ORDER BY trading_day;

Result:

(2 rows)

date_part, extract

Syntax:

date_part(field, t)
extract(field FROM t)
extract(field, t)

Takes one part of a time: the year, the month, the hour, the weekday, the week number and so on. date_part('month', '2026-09-10') is 9. extract(month FROM t) is the SQL standard way to write the same thing; extract('month', t) also works.

• Returns: LONG; DOUBLE for second and epoch.
• NULL: NULL when field or t is NULL.
• Notes: UTC, like every function on this page: add 20700000 for Nepal time. An unknown field is an error that lists the fields. extract(field FROM t) is PostgreSQL style; date_part has the same result.

Example

SELECT date_part('year', '2026-09-10T05:20:30.250Z') AS year,
       extract(month FROM '2026-09-10T05:20:30.250Z') AS month,
       extract('dow', '2026-09-10') AS dow,
       date_part('isodow', '2026-09-13') AS isodow_sunday,
       date_part('doy', CAST('2026-09-10' AS DATE)) AS doy,
       date_part('second', '2026-09-10T05:20:30.250Z') AS second,
       date_part('epoch', '2026-09-10T00:00:00Z') AS epoch;

Result:

(1 row)

Example: App sales per year and quarter (sample data: analysis-finance)

SELECT extract(year FROM invoice_date) AS year, extract(quarter FROM invoice_date) AS quarter,
       count(*) AS invoices, sum(amount) AS sales
FROM invoices
GROUP BY extract(year FROM invoice_date), extract(quarter FROM invoice_date)
ORDER BY year, quarter;

Result:

(6 rows)

Example: Traded volume per Nepal weekday (0 = Sunday) (sample data: sample)

SELECT date_part('dow', time + 20700000) AS nepal_weekday, count(*) AS minutes, sum(volume) AS volume
FROM ochl_minutes
GROUP BY date_part('dow', time + 20700000)
ORDER BY nepal_weekday;

Result:

(2 rows)

Example: An unknown field lists the fields you can use (shows an error)

SELECT date_part('fortnight', '2026-09-10');

Result:

Error: line 1, column 8: date_part: unknown field 'fortnight'; use year, quarter, month, week, day, dow, isodow, doy, hour, minute, second, millisecond, epoch, isoyear, decade or century

quarter

Syntax:

quarter(t)

The quarter of the year of the UTC date: 1 for January-March, 2 for April-June, 3 for July-September, 4 for October-December. For a fiscal year that does not start in January, use fiscal_quarter.

• Returns: LONG.
• NULL: quarter(NULL) is NULL.
• Notes: the same as date_part('quarter', t) and to_char(t, 'Q'). For the first day of the quarter use date_trunc('quarter', t).

Example

SELECT quarter('2026-01-01') AS jan, quarter('2026-06-30') AS jun, quarter('2026-09-10T05:15:00Z') AS sep, quarter(CAST('2026-12-31' AS DATE)) AS dec;

Result:

(1 row)

Example: Volume of every symbol per quarter (sample data: sample)

SELECT year(time) AS year, quarter(time) AS quarter, symbol, count(*) AS days, sum(volume) AS volume
FROM ochl_day
GROUP BY year(time), quarter(time), symbol
ORDER BY year, quarter, symbol;

Result:

(6 rows)

day_of_year

Syntax:

day_of_year(t)

The day number in the year of the UTC date: 1 for 1 January, 365 for 31 December (366 in a leap year).

• Returns: LONG.
• NULL: day_of_year(NULL) is NULL.
• Notes: the same as date_part('doy', t). Useful to compare the same day of different years ("how far into the year are we?").

Example

SELECT day_of_year('2026-01-01') AS first_day, day_of_year('2026-09-10') AS today, day_of_year('2026-12-31') AS last_day, day_of_year(CAST('2024-12-31' AS DATE)) AS leap_year;

Result:

(1 row)

Example: How much of the year had passed on each candle day? (sample data: sample)

SELECT time, day_of_year(time) AS day_no, round(day_of_year(time) * 100.0 / 365, 1) AS year_done_pct
FROM ochl_day
WHERE symbol = 'CITY' AND time >= '2026-09-08'
ORDER BY time;

Result:

(3 rows)

week_of_year

Syntax:

week_of_year(t)

The ISO week number of the UTC date, from 1 to 53. ISO weeks start on Monday, and week 1 is the week that has the first Thursday of the year. So the first days of January can be in week 52 or 53 of the year before, and the last days of December in week 1 of the next year.

• Returns: LONG.
• NULL: week_of_year(NULL) is NULL.
• Notes: the same as date_part('week', t). When you group by week across a new year, also group by date_part('isoyear', t), not by year(t). A Sunday-to-Thursday trading week (as on some exchanges) starts on Sunday: for that, use date_trunc('week', t) instead.

Example

SELECT week_of_year('2026-09-10') AS thursday, week_of_year('2026-01-01') AS new_year_2026,
       week_of_year('2027-01-01') AS new_year_2027, date_part('isoyear', '2027-01-01') AS its_iso_year;

Result:

(1 row)

Example: Forum posts per ISO week (sample data: fn-dates)

SELECT date_part('isoyear', created_at) AS iso_year, week_of_year(created_at) AS week, count(*) AS posts
FROM forum_posts
GROUP BY date_part('isoyear', created_at), week_of_year(created_at)
ORDER BY iso_year, week;

Result:

(5 rows)

date_trunc

Syntax:

date_trunc(unit, t)
date_trunc(unit, t, time_zone)

Cuts the time down to the start of its year, month, week, day, hour, minute or second. date_trunc('month', '2026-09-10T08:30:00Z') is 2026-09-01T00:00:00.000Z. Use it to put rows into groups by time.

• Returns: a DATE when t is a DATE and there is no zone; else a TIMESTAMP. With a zone the result is the start on that clock, as a UTC time: date_trunc('day', '2026-09-10T02:00:00Z', 'Asia/Kathmandu') is 2026-09-09T18:15:00.000Z (Nepal midnight of 10 Sep).
• NULL: NULL when any argument is NULL.
• Notes:
  • Without a zone: the session's clock, which is UTC unless set. Then date_trunc('day', ...) gives UTC midnight, which is 05:45 in Nepal. Give the zone, or SET TIME ZONE 'Asia/Kathmandu', for Nepal days, as SAMPLE BY ... TIME ZONE does.
  • Zones that change for summer time (like Europe/London) are refused.
  • 'week' starts on Sunday (as in a Sunday-to-Thursday trading week).
  • Careful: 'w' and 'weeks' are not the same as 'week'. They make 7-day steps counted from 1970-01-01, which was a Thursday, so they start on Thursday.
  • 'm' is a minute, not a month. 'quarter' gives 1 January, 1 April, 1 July or 1 October (only this spelling; for the quarter number use quarter).
  • For candles of any size (15 minutes, 1 hour) use SAMPLE BY.

Example

SELECT date_trunc('year', '2026-09-10T08:30:00Z') AS year, date_trunc('month', '2026-09-10T08:30:00Z') AS month, date_trunc('week', '2026-09-10T08:30:00Z') AS week_sunday, date_trunc('hour', '2026-09-10T08:30:00Z') AS hour, date_trunc('month', CAST('2026-09-10' AS DATE)) AS date_stays_date;

Result:

(1 row)

Example: Forum posts per week (sample data: fn-dates)

SELECT date_trunc('week', created_at) AS week, count(*) AS posts, count(DISTINCT author) AS authors
FROM forum_posts
GROUP BY date_trunc('week', created_at)
ORDER BY week;

Result:

(4 rows)

Example: Monthly candles of CITY from daily candles (sample data: sample)

SELECT date_trunc('month', time) AS month,
       first(open) AS open, max(high) AS high, min(low) AS low, last(close) AS close
FROM ochl_day
WHERE symbol = 'CITY'
GROUP BY date_trunc('month', time)
ORDER BY month;

Result:

(3 rows)

Example: 'week' and 'w' are different

SELECT date_trunc('week', '2026-09-10') AS week_from_sunday, date_trunc('w', '2026-09-10') AS w_from_thursday;

Result:

(1 row)

Example: Start of the quarter: sales per quarter (sample data: analysis-finance)

SELECT date_trunc('quarter', invoice_date) AS quarter_start, count(*) AS invoices, sum(amount) AS sales
FROM invoices
GROUP BY date_trunc('quarter', invoice_date)
ORDER BY quarter_start;

Result:

(6 rows)

Example: Decade is not a unit (shows an error)

SELECT date_trunc('decade', '2026-09-10');

Result:

Error: line 1, column 8: date_trunc: unit must be year, month, week, day, hour, minute, second or ms

Example: The UTC day and the Nepal day of the same moments

SELECT t, date_trunc('day', t) AS utc_midnight, date_trunc('day', t, 'Asia/Kathmandu') AS nepal_midnight,
       date_trunc('month', t, 'Asia/Kathmandu') AS nepal_month
FROM (SELECT CAST('2026-09-09T20:00:00Z' AS TIMESTAMP) AS t
      UNION ALL SELECT CAST('2026-08-31T19:00:00Z' AS TIMESTAMP)) x
ORDER BY t;

Result:

(2 rows)

Example: Sign-ups per Nepal day, as times (sample data: fn-dates)

SELECT date_trunc('day', created_at, 'Asia/Kathmandu') AS nepal_day, count(*) AS signups
FROM signups
GROUP BY date_trunc('day', created_at, 'Asia/Kathmandu')
ORDER BY nepal_day;

Result:

(5 rows)

timestamp_floor

Syntax:

timestamp_floor(unit, t)
timestamp_floor(unit, t, time_zone)
timestamp_floor(unit, t, origin)

The QuestDB function: cuts the time down to a whole number of units. timestamp_floor('15m', '2026-09-10T05:22:10Z') is 2026-09-10T05:15:00.000Z.

• Returns: TIMESTAMP, in UTC. With a zone it is the start on that clock: timestamp_floor('1d', t, 'Asia/Kathmandu') is Nepal midnight, shown as 18:15 UTC the day before.
• NULL: NULL when any argument is NULL.
• Notes:
  • Counts start at 1970-01-01 00:00 (on the zone's clock when one is given): '2d' gives days 0, 2, 4, ... from then, '3M' gives January, April, July and October. Weeks start on Monday; for Sunday weeks use date_trunc('week', t) or SAMPLE BY ... WEEK START SUNDAY.
  • U (microseconds) is not a unit: KDB keeps milliseconds.
  • For candles, SAMPLE BY does the same cut and the aggregates in one step.

Example: Cut one moment by several units

SELECT timestamp_floor('15m', '2026-09-10T05:22:10Z') AS quarter_hour,
       timestamp_floor('1d', '2026-09-09T20:00:00Z') AS utc_day,
       timestamp_floor('1d', '2026-09-09T20:00:00Z', 'Asia/Kathmandu') AS nepal_day,
       timestamp_floor('w', '2026-09-10T05:00:00Z') AS monday,
       timestamp_floor('3M', '2026-09-10T05:00:00Z') AS quarter_month,
       timestamp_floor('2d', '2026-09-10T05:00:00Z', '2026-09-01') AS two_days_from_sep_1;

Result:

(1 row)

Example: Nepal-hour candles with timestamp_floor, like SAMPLE BY 1h TIME ZONE (sample data: sample)

SELECT timestamp_floor('1h', time, 'Asia/Kathmandu') AS hour, first(open) AS open, last(close) AS close, count(*) AS minutes
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10'
GROUP BY timestamp_floor('1h', time, 'Asia/Kathmandu')
ORDER BY hour;

Result:

(4 rows)

Example: An unknown unit (shows an error)

SELECT timestamp_floor('1x', '2026-09-10');

Result:

Error: line 1, column 8: timestamp_floor: unit must be T, s, m, h, d, w, M or y with an optional count, like '15m' or '1d'; got '1x'

timestamp_floor_utc(unit, t [, zone]) is QuestDB's name for the same function: its answer is in UTC, and KDB's always is.

timestamp_ceil

Syntax:

timestamp_ceil(unit, t)
timestamp_ceil(unit, t, time_zone)
timestamp_ceil(unit, t, origin)

The QuestDB function: moves the time up to the next whole unit. A time that is already on a whole unit stays as it is: timestamp_ceil('1h', '06:00') is 06:00.

• Returns: TIMESTAMP, in UTC.
• NULL: NULL when any argument is NULL.

Example: Round up to the quarter hour, the day and the month

SELECT timestamp_ceil('15m', '2026-09-10T05:22:10Z') AS quarter_hour,
       timestamp_ceil('15m', '2026-09-10T05:30:00Z') AS already_whole,
       timestamp_ceil('1d', '2026-09-10T05:22:10Z') AS next_midnight,
       timestamp_ceil('M', '2026-09-10T05:22:10Z') AS next_month;

Result:

(1 row)

today, yesterday, tomorrow

Syntax:

today()
today(time_zone)
today(t)
yesterday(...)
tomorrow(...)

QuestDB's day intervals: the whole day, from 00:00 to 23:59:59.999. Use them after IN: WHERE time IN today() (interval lists).

• Returns: the interval as text, ('2026-09-10T00:00:00.000Z', '2026-09-10T23:59:59.999Z'). interval_start and interval_end take the two ends out.
• Notes: the day is taken once, when the statement starts.

Example: Today's rows, and the start of tomorrow (sample data: sample)

SELECT count(*) AS rows_today, interval_start(tomorrow()) AS tomorrow_starts
FROM live_data WHERE timestamp IN today();

Result:

(1 row)

Example: The day of a given time, and the day before it

SELECT today('2026-09-10T20:00:00Z') AS that_day, interval_start(yesterday('2026-09-10T20:00:00Z')) AS day_before;

Result:

(1 row)

interval, interval_start, interval_end

Syntax:

interval(from, to)
interval_start(an_interval)
interval_end(an_interval)

interval makes a QuestDB interval from two times, both included. interval_start and interval_end give its first and last moment.

• Returns: interval gives text ('from', 'to'); interval_start and interval_end give a TIMESTAMP.
• NULL: NULL when an argument is NULL.

Example: An interval and its two ends

SELECT interval('2026-09-10T05:15:00Z', '2026-09-10T09:15:00Z') AS session,
       interval_start(interval('2026-09-10T05:15:00Z', '2026-09-10T09:15:00Z')) AS opens,
       interval_end(interval('2026-09-10T05:15:00Z', '2026-09-10T09:15:00Z')) AS closes;

Result:

(1 row)

Example: The end must not be before the start (shows an error)

SELECT interval('2026-09-10', '2026-09-09');

Result:

Error: line 1, column 8: interval: the end 2026-09-09T00:00:00.000Z is before the start 2026-09-10T00:00:00.000Z

days_in_month, is_leap_year, is_end_of_month, day_of_week_sunday_first, millis, to_pg_date

Syntax:

days_in_month(t)
is_leap_year(t)
is_leap_year(year)
is_end_of_month(t)
day_of_week_sunday_first(t)
millis(t)
to_pg_date(t)

Small QuestDB calendar functions.

• NULL: NULL for a NULL argument.
• Notes: the day is taken on the session's time zone, as for day_of_week. KDB's own day_of_week is 0 Sunday to 6 Saturday; a database set to the QuestDB dialect numbers it as QuestDB does, 1 Monday to 7 Sunday.

Example: Facts about a date

SELECT days_in_month('2028-02-10') AS feb_2028_days,
       is_leap_year('2028-02-10') AS leap_2028,
       is_leap_year(2026) AS leap_2026,
       is_end_of_month('2026-09-30') AS month_end,
       day_of_week_sunday_first('2026-09-27') AS sunday,
       millis('2026-09-10T05:15:00.250Z') AS ms,
       to_pg_date('2026-09-10T20:00:00Z') AS day;

Result:

(1 row)

sysdate

Syntax:

sysdate()

QuestDB's clock: the time now, the same as now().

• Returns: TIMESTAMP (KDB keeps milliseconds, which is what QuestDB's DATE holds).

Example: sysdate is now

SELECT sysdate() = now() AS same;

Result:

(1 row)

timestamp_sequence, timestamp_shuffle

Syntax:

timestamp_sequence(start, step_microseconds)
timestamp_shuffle(from, to)

QuestDB's test-data helpers. timestamp_sequence gives start on the first row, start + step on the next, and so on. timestamp_shuffle gives a random time from from to to.

• Returns: TIMESTAMP.
• Notes: each use of timestamp_sequence in a query counts its own rows.

Example: One row a minute, with long_sequence

SELECT x, timestamp_sequence('2026-09-10T05:15:00Z', 60000000) AS time
FROM long_sequence(4);

Result:

(4 rows)

Example: Random times in one day

SELECT count(*) AS inside FROM (SELECT timestamp_shuffle('2026-09-10', '2026-09-11') AS t FROM long_sequence(100)) s
WHERE t >= '2026-09-10' AND t <= '2026-09-11';

Result:

(1 row)

to_timezone, to_utc

Syntax:

to_timezone(t, time_zone)
to_utc(t, time_zone)

The QuestDB functions. to_timezone shows a UTC time on the clock of a zone: to_timezone('2026-09-10T05:15:00Z', 'Asia/Kathmandu') is 2026-09-10T11:00:00.000Z. to_utc is the other way round: it reads t as that zone's clock and gives the UTC time.

• Returns: TIMESTAMP.
• NULL: NULL when any argument is NULL.
• Notes:
  • The result of to_timezone holds the Nepal clock time but still ends with Z. Use it for hour(), day() and showing, not to store.
  • to_utc(t, 'Asia/Kathmandu') is the same as t AT TIME ZONE 'Asia/Kathmandu'.
  • Zones that change for summer time are refused.

Example: UTC to the Nepal clock and back

SELECT to_timezone('2026-09-10T05:15:00Z', 'Asia/Kathmandu') AS nepal_clock,
       to_utc('2026-09-10T11:00:00Z', 'Asia/Kathmandu') AS utc,
       hour(to_timezone('2026-09-10T05:15:00Z', 'Asia/Kathmandu')) AS nepal_hour;

Result:

(1 row)

Example: A zone with summer time: London is +01:00 in September, +00:00 in January

SELECT to_timezone('2026-09-10T05:15:00Z', 'Europe/London') AS september, to_timezone('2026-01-10T05:15:00Z', 'Europe/London') AS january;

Result:

(1 row)

last_day

Syntax:

last_day(t)

The last day of the month of the UTC date: last_day('2026-02-10') is 2026-02-28. Accountants use it for the month-end date of a report (like Excel EOMONTH(date, 0)).

• Returns: DATE, also for a TIMESTAMP.
• NULL: last_day(NULL) is NULL.
• Notes: knows leap years (29 February). For the first day of the month use date_trunc('month', t). For the month end of the next month: last_day(dateadd('month', 1, t)).

Example

SELECT last_day('2026-02-10') AS feb, last_day('2028-02-10T10:00:00Z') AS leap_feb, last_day(CAST('2026-09-10' AS DATE)) AS sep,
       last_day(dateadd('month', 1, '2026-09-10')) AS next_month_end;

Result:

(1 row)

Example: Month-end report date of every invoice (sample data: analysis-finance)

SELECT last_day(invoice_date) AS month_end, count(*) AS invoices, sum(amount) AS sales
FROM invoices
WHERE invoice_date >= '2026-01-01'
GROUP BY last_day(invoice_date)
ORDER BY month_end;

Result:

(6 rows)

dateadd

Syntax:

dateadd(unit, amount, t)

Adds amount units to the time. A negative amount goes back. dateadd('d', 7, '2026-09-10') is one week later.

• Returns: a DATE when t is a DATE; else a TIMESTAMP.
• NULL: NULL when any argument is NULL.
• Notes:
  • Months and years follow the calendar. When the day does not exist in the new month, you get the last day of that month: 31 January + 1 month is 28 February.
  • 'm' is minutes. Write 'month' or 'mo' for months.
  • With a DATE, the result is a DATE, so hours are cut off: dateadd('h', 30, date) gives the next day.
  • amount must be a whole number: 1.5 is an error. For 1.5 days use 36 hours.
  • A TIMESTAMP plus a number of milliseconds (time + 3600000) also works; see arithmetic operators.

Example

SELECT dateadd('d', 7, '2026-09-10T05:15:00Z') AS next_week, dateadd('month', 1, '2026-01-31') AS end_of_feb, dateadd('y', 1, CAST('2024-02-29' AS DATE)) AS leap_day_plus_year, dateadd('m', -90, '2026-09-10T05:15:00Z') AS minus_90_minutes, dateadd('h', 30, CAST('2026-09-10' AS DATE)) AS date_stays_date;

Result:

(1 row)

Example: When does the 7-day free trial of each new user end? (sample data: fn-dates)

SELECT id, email, created_at, dateadd('d', 7, created_at) AS trial_ends
FROM signups
WHERE created_at >= '2026-09-03'
ORDER BY id;

Result:

(5 rows)

Example: The 10-year listing anniversary of each company (sample data: fn-dates)

SELECT symbol, listed_on, dateadd('y', 10, listed_on) AS ten_years
FROM listings
ORDER BY listed_on;

Result:

(5 rows)

Example: The amount must be a whole number (shows an error)

SELECT dateadd('d', 1.5, '2026-09-10');

Result:

Error: line 1, column 8: dateadd: the amount must be a whole number

datediff

Syntax:

datediff(unit, from, to)

How many whole units there are from from to to. datediff('d', '2026-09-01', '2026-09-10') is 9.

• Returns: LONG.
• NULL: NULL when any argument is NULL.
• Notes:
  • The result is negative when to is before from.
  • Parts are dropped, not rounded: 5 hours 59 minutes is 5 hours. For a negative result they are dropped toward zero (-5, not -6).
  • Months and years count whole calendar months, like dateadd: from 10 January 10:00 to 10 February 09:00 is 0 months (one hour short), and from 31 January to 28 February is 1 month (the month end is reached). Years are whole months / 12. For the months, days and hours together use age.
  • The order of the arguments is like SQL Server (unit, from, to).

Example

SELECT datediff('d', '2026-09-01', '2026-09-10') AS days, datediff('d', '2026-09-10', '2026-09-01') AS negative, datediff('h', '2026-09-10T00:00:00Z', '2026-09-10T05:59:00Z') AS hours_cut, datediff('m', '2026-09-10T11:00:00+05:45', '2026-09-10T15:00:00+05:45') AS market_minutes;

Result:

(1 row)

Example: How long each company has been listed (up to 2026-09-11) (sample data: fn-dates)

SELECT symbol, listed_on,
       datediff('d', listed_on, '2026-09-11') AS days,
       datediff('w', listed_on, '2026-09-11') AS weeks,
       year('2026-09-11') - year(listed_on) AS calendar_years
FROM listings
ORDER BY listed_on;

Result:

(5 rows)

Example: First and last day of the sample candles (sample data: sample)

SELECT min(time) AS first_day, max(time) AS last_day, datediff('d', min(time), max(time)) AS days_between
FROM ochl_day;

Result:

(1 row)

Example: Whole months and years between two dates

SELECT datediff('month', '2026-01-01', '2026-09-10') AS months,
       datediff('month', '2026-01-10T10:00:00Z', '2026-02-10T09:00:00Z') AS one_hour_short,
       datediff('month', '2026-01-31', '2026-02-28') AS to_month_end,
       datediff('y', '2025-09-11', '2026-09-10') AS one_day_short,
       datediff('years', '1998-07-28', '2026-09-10') AS years;

Result:

(1 row)

Example: Quarter is not a unit of datediff (shows an error)

SELECT datediff('quarter', '2026-01-01', '2026-09-10');

Result:

Error: line 1, column 8: datediff: unit must be y, month, w, d, h, m, s or ms

age

Syntax:

age(t)
age(to, from)

The time between two times, written like a person says it: '28 years 1 mon 13 days'. With one argument it is the time from t to today (today's UTC midnight), like PostgreSQL. Use it to show how old a company listing, an account or a user is.

• Returns: STRING with years, months (mon), days, and hh:mm:ss when there are hours (plus .mmm for milliseconds). Parts that are 0 are left out; equal times give '00:00:00'.
• NULL: NULL when an argument is NULL.
• Notes: the order is later first, like PostgreSQL. When to is before from, every part is negative: '-1 mons -1 days'. The result is text, so you cannot add or compare it as a number: for a number use datediff. With one argument the result changes every day.

Example

SELECT age('2026-09-10', '1998-07-28') AS listed_age,
       age('2026-09-10T15:30:00Z', '2026-09-10T11:00:00Z') AS hours,
       age('2026-09-11', '2025-09-11') AS one_year,
       age('2026-03-01', '2026-01-31') AS month_and_day,
       age('2026-01-31', '2026-03-01') AS negative;

Result:

(1 row)

Example: How long each company has been listed (up to 2026-09-11) (sample data: fn-dates)

SELECT symbol, listed_on, age('2026-09-11', listed_on) AS listed_for
FROM listings
ORDER BY listed_on;

Result:

(5 rows)

Example: One argument: counted up to today's midnight (UTC)

SELECT age('2000-01-01') = age(date_trunc('day', now()), '2000-01-01') AS same;

Result:

(1 row)

make_date

Syntax:

make_date(year, month, day)

Builds a DATE from three numbers: make_date(2026, 9, 10) is 2026-09-10. Useful when a table keeps the year, month and day in separate columns, or to build the start of a fiscal year.

• Returns: DATE.
• NULL: NULL when an argument is NULL.
• Notes: a date that does not exist (30 February) is an error, not the next month. A number with a fraction (2026.5) is an error.

Example

SELECT make_date(2026, 9, 10) AS d, make_date(2028, 2, 29) AS leap_day, make_date(year('2026-09-10'), 7, 17) AS fiscal_year_start;

Result:

(1 row)

Example: Invoices from the start of Nepal's fiscal year 2083/84 (sample data: analysis-finance)

SELECT id, invoice_date, customer, amount
FROM invoices
WHERE invoice_date >= make_date(2026, 7, 17)
ORDER BY invoice_date;

Result:

(3 rows)

Example: 30 February does not exist (shows an error)

SELECT make_date(2026, 2, 30);

Result:

Error: line 1, column 8: make_date: date 2026-2-30 does not exist

make_timestamp

Syntax:

make_timestamp(year, month, day, hour, minute, second)

Builds a TIMESTAMP from its parts. The parts are UTC. second may have a fraction: 30.5 is 30 seconds and 500 milliseconds.

• Returns: TIMESTAMP (UTC).
• NULL: NULL when an argument is NULL.
• Notes: a date that does not exist, or a time out of range (24:00), is an error. For a Nepal clock time subtract 20700000 ms (5 h 45 min): make_timestamp(2026, 9, 10, 11, 0, 0) - 20700000 is 11:00 in Kathmandu.

Example

SELECT make_timestamp(2026, 9, 10, 5, 15, 30.5) AS utc_time,
       make_timestamp(2026, 9, 10, 11, 0, 0) - 20700000 AS market_opens;

Result:

(1 row)

Example: Hour 24 is out of range (shows an error)

SELECT make_timestamp(2026, 9, 10, 24, 0, 0);

Result:

Error: line 1, column 8: make_timestamp: the time of day is out of range

to_char

Syntax:

to_char(t, format)
to_char(t, format, time_zone)

Writes a time as text in the format you want. to_char('2026-09-10T08:30:00Z', 'DD Mon YYYY') is 10 Sep 2026. With a time zone, the time is first moved to that zone: this is the easy way to show Nepal time. To write a number (to_char(1234.5, 'FM999,999.00')), see to_char for numbers.

Format codes (upper and lower case matter):

Time zones: UTC, GMT, Z, Etc/UTC, Asia/Kathmandu, Asia/Katmandu, NPT, Asia/Kolkata, Asia/Calcutta, IST, Asia/Colombo, Asia/Dhaka, Asia/Thimphu, Asia/Karachi, Asia/Dubai, Asia/Bangkok, Asia/Jakarta, Asia/Shanghai, Asia/Hong_Kong, Asia/Singapore, Asia/Taipei, Asia/Tokyo, Asia/Seoul (any case), or an offset with a sign: '+05:45', '-03:00'.

• Returns: STRING.
• NULL: NULL when any argument is NULL (also a NULL time zone).
• Notes:
  • Every other character is copied as it is: -, /, :, spaces, and letters that are not a code.
  • Careful with words: letters that are a code are replaced. 'Date: DD' gives 5ate: 10, because D is the weekday number. Put words in double quotes: '"Date:" DD'.
  • Zones that change for summer time (like Europe/London) are refused. An offset needs its sign: '05:45' and '+0545' are errors.
  • WW (week of the year) and other PostgreSQL codes are not supported; they are copied as text.
  • The result is text, so ORDER BY on it sorts like text: 'Aug 2026' comes before 'Sep 2025'. Also select a sortable form like 'YYYY-MM' and order by that (after GROUP BY, ORDER BY can only use result columns).

Example

SELECT to_char('2026-09-10T15:30:05.042Z', 'YYYY-MM-DD HH24:MI:SS.MS') AS iso,
       to_char('2026-09-10T15:30:05.042Z', 'Dy, DD Mon YYYY HH12:MI AM') AS english,
       to_char('2026-09-10T15:30:05.042Z', 'DDD Q D') AS day_of_year_quarter_weekday,
       to_char(CAST('2026-09-10' AS DATE), 'DD/MM/YYYY') AS from_date;

Result:

(1 row)

Example: Show a UTC time in Nepal time

SELECT to_char('2026-09-10T05:15:00Z', 'YYYY-MM-DD HH24:MI') AS utc,
       to_char('2026-09-10T05:15:00Z', 'YYYY-MM-DD HH24:MI', 'Asia/Kathmandu') AS nepal,
       to_char('2026-09-10T05:15:00Z', 'HH12:MI am', '+05:45') AS nepal_12h,
       to_char('2026-09-10T05:15:00Z', 'HH24:MI', 'Asia/Tokyo') AS tokyo;

Result:

(1 row)

Example: Last COAST trades of the day with the Nepal clock time (sample data: sample)

SELECT to_char(time, 'HH24:MI', 'Asia/Kathmandu') AS nepal_time, close, volume
FROM ochl_minutes
WHERE symbol = 'COAST' AND time IN '2026-09-10'
ORDER BY time DESC
LIMIT 3;

Result:

(3 rows)

Example: App sign-ups per Nepal date (sample data: fn-dates)

SELECT to_char(created_at, 'YYYY-MM-DD', 'Asia/Kathmandu') AS nepal_date, count(*) AS signups
FROM signups
GROUP BY to_char(created_at, 'YYYY-MM-DD', 'Asia/Kathmandu')
ORDER BY nepal_date;

Result:

(5 rows)

Example: Month report with month names (sample data: sample)

SELECT to_char(time, 'YYYY-MM') AS sort_key, to_char(time, 'Mon YYYY') AS month,
       round(sum(close * volume) / 1000000, 2) AS turnover_millions
FROM ochl_day
WHERE symbol = 'VOLT'
GROUP BY to_char(time, 'YYYY-MM'), to_char(time, 'Mon YYYY')
ORDER BY sort_key;

Result:

(3 rows)

Example: Words in the format: use double quotes

SELECT to_char('2026-09-10', 'Date: DD') AS wrong, to_char('2026-09-10', '"Date:" DD') AS right;

Result:

(1 row)

Example: Zones with summer time are refused (shows an error)

SELECT to_char('2026-09-10T05:15:00Z', 'HH24:MI', 'Europe/London');

Result:

Error: line 1, column 8: to_char: time zone 'Europe/London' is not supported (it may change for daylight saving); use a fixed offset like '+05:45'

fiscal_year

Syntax:

fiscal_year(t)
fiscal_year(t, start_month)
fiscal_year(t, start_month, start_day)

The fiscal year (financial year) of a date, as the calendar year in which that fiscal year starts. A fiscal year is the 12-month year a company or a government uses for its accounts and taxes. It often does not start on 1 January: in Nepal it starts in mid-July, in India on 1 April.

• Returns: LONG: 2025 means the fiscal year that started in 2025 (for Nepal: 2025-07-17 to 2026-07-16).
• NULL: NULL when an argument is NULL.
• Notes:
  • The default start is 17 July, about Shrawan 1, the start of Nepal's fiscal year. For Nepal's own name of the year (like 2082/83) use nepal_fiscal_year.
  • Careful: start_day has the default 17 also when you give only start_month. For India write fiscal_year(t, 4, 1), not fiscal_year(t, 4).
  • start_month outside 1-12 or start_day outside 1-28 is an error.
  • The date is the UTC date: add 20700000 for a Nepal date just after midnight.

Example

SELECT fiscal_year('2026-07-16') AS last_day_of_fy_2025, fiscal_year('2026-07-17') AS first_day_of_fy_2026,
       fiscal_year('2026-03-31', 4, 1) AS india_before_april, fiscal_year('2026-04-01', 4, 1) AS india_april,
       fiscal_year('2026-09-10', 1, 1) AS calendar_year;

Result:

(1 row)

Example: Sales per fiscal year (Nepal start, 17 July) (sample data: analysis-finance)

SELECT fiscal_year(invoice_date) AS fiscal_year, count(*) AS invoices, sum(amount) AS sales
FROM invoices
GROUP BY fiscal_year(invoice_date)
ORDER BY fiscal_year;

Result:

(3 rows)

Example: Day 29 or later cannot start a fiscal year (shows an error)

SELECT fiscal_year('2026-09-10', 1, 31);

Result:

Error: line 1, column 8: fiscal_year: the start month must be 1-12 and the start day 1-28

fiscal_quarter

Syntax:

fiscal_quarter(t)
fiscal_quarter(t, start_month)
fiscal_quarter(t, start_month, start_day)

The quarter inside the fiscal year, from 1 to 4. Each fiscal quarter is 3 months counted from the start day: with the Nepal start (17 July), quarter 1 is 17 July to 16 October, quarter 2 is 17 October to 16 January, and so on. Banks and listed companies in Nepal publish their quarterly reports by these quarters.

• Returns: LONG, 1 to 4.
• NULL: NULL when an argument is NULL.
• Notes: the same start rules as fiscal_year (also the default day 17 when only start_month is given). With fiscal_quarter(t, 1, 1) it is the calendar quarter, like quarter.

Example

SELECT fiscal_quarter('2026-07-17') AS q1_start, fiscal_quarter('2026-10-16') AS q1_end, fiscal_quarter('2026-10-17') AS q2_start,
       fiscal_quarter('2027-07-16') AS q4_end, fiscal_quarter('2026-09-10', 4, 1) AS india, fiscal_quarter('2026-09-10', 1, 1) AS calendar;

Result:

(1 row)

Example: Quarterly sales report of the fiscal year (sample data: analysis-finance)

SELECT nepal_fiscal_year(invoice_date) AS fiscal_year, fiscal_quarter(invoice_date) AS quarter,
       count(*) AS invoices, sum(amount) AS sales
FROM invoices
GROUP BY nepal_fiscal_year(invoice_date), fiscal_quarter(invoice_date)
ORDER BY fiscal_year, quarter;

Result:

(6 rows)

nepal_fiscal_year

Syntax:

nepal_fiscal_year(t)

The name of Nepal's fiscal year of a date, as Nepal writes it in the Bikram Sambat (BS) calendar: '2083/84'. Nepal's fiscal year (arthik barsa) starts on Shrawan 1, which is about 16 or 17 July, and ends at the end of Ashadh the next year. Companies, banks and the tax office in Nepal use it for their reports.

• Returns: STRING like '2082/83': the BS year in which the fiscal year starts, a slash, and the last two digits of the next BS year.
• NULL: nepal_fiscal_year(NULL) is NULL.
• Notes:
  • KDB does not convert dates to the BS calendar. It uses a fixed start of 17 July every year and adds 57 years. Shrawan 1 moves by a day between years, so a date on 16 or 17 July can be in the wrong year; check those days with the official calendar when it matters.
  • The same as fiscal_year(t) plus 57, written as a text. The date is the UTC date.

Example

SELECT nepal_fiscal_year('2026-07-16') AS before_shrawan, nepal_fiscal_year('2026-07-17') AS shrawan_1,
       nepal_fiscal_year('2026-09-10') AS today, nepal_fiscal_year(CAST('2000-01-01' AS DATE)) AS year_2000;

Result:

(1 row)

Example: Sales per Nepal fiscal year (sample data: analysis-finance)

SELECT nepal_fiscal_year(invoice_date) AS fiscal_year, min(invoice_date) AS first_invoice, max(invoice_date) AS last_invoice,
       count(*) AS invoices, sum(amount) AS sales
FROM invoices
GROUP BY nepal_fiscal_year(invoice_date)
ORDER BY fiscal_year;

Result:

(3 rows)

Example: Expenses of the accounting book per fiscal year (sample data: query-app)

SELECT nepal_fiscal_year(l.entry_date) AS fiscal_year, a.name AS expense, sum(l.debit) AS amount
FROM ledger l
JOIN accounts a ON a.code = l.account
WHERE a.type = 'expense'
GROUP BY nepal_fiscal_year(l.entry_date), a.name
ORDER BY fiscal_year, expense;

Result:

(2 rows)

Local time, for example Nepal

Syntax:

'2026-09-10T11:00:00+05:45'        -- write Nepal times with +05:45
to_timezone(time, 'Asia/Kathmandu')  -- the Nepal clock time (also time + 20700000)
to_char(time, format, 'Asia/Kathmandu')
time IN '2026-09-10' TIME ZONE 'Asia/Kathmandu'
date_trunc('day', time, 'Asia/Kathmandu')
SAMPLE BY 1h TIME ZONE 'Asia/Kathmandu'

KDB stores and shows every time in UTC. These tips use Nepal time as the example local zone: UTC + 5 hours 45 minutes, all year (Nepal has no summer time). For another zone change the offset and the milliseconds, for example -05:00 and - 18000000 for New York in winter. In a zone with summer time the fixed number is right for only part of the year.

• Notes: to_timezone(...) and + 20700000 give a TIMESTAMP that is shown with Z but holds the Nepal clock time. Use them inside hour(), day() and so on, not to store. NPT and Asia/Kathmandu are the same zone. More in time and time zones.

Example: QuestDB to_timezone and to_utc, and the older ways

SELECT to_timezone('2026-09-10T05:15:00Z', 'Asia/Kathmandu') AS to_timezone,
       CAST('2026-09-10T05:15:00Z' AS TIMESTAMP) + 20700000 AS plus_ms,
       to_utc('2026-09-10T11:00:00Z', 'Asia/Kathmandu') AS to_utc,
       CAST('2026-09-10T11:00:00Z' AS TIMESTAMP) AT TIME ZONE 'Asia/Kathmandu' AS at_time_zone;

Result:

(1 row)

Example: One market opening minute (11:00 in Nepal), three ways

SELECT CAST('2026-09-10T11:00:00+05:45' AS TIMESTAMP) AS stored_utc,
       hour('2026-09-10T11:00:00+05:45') AS utc_hour,
       hour(CAST('2026-09-10T11:00:00+05:45' AS TIMESTAMP) + 20700000) AS nepal_hour;

Result:

(1 row)

Example: All minute candles of one Nepal trading day (sample data: sample)

SELECT count(*) AS candles, min(time) AS first_utc, max(time) AS last_utc
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10' TIME ZONE 'Asia/Kathmandu';

Result:

(1 row)

Example: Hourly COAST candles on the Nepal clock (sample data: sample)

SELECT time, count(*) AS minutes, first(open) AS open, last(close) AS close
FROM ochl_minutes
WHERE symbol = 'COAST' AND time IN '2026-09-10'
SAMPLE BY 1h TIME ZONE 'Asia/Kathmandu';

Result:

(4 rows)

current_date

Syntax:

current_date
current_date()

Today's date on the session's clock (UTC unless SET TIME ZONE names a zone). The PostgreSQL name; KDB gives the same as CAST(now() AS DATE).

• Returns: DATE.
• NULL: never NULL.
• Notes: UTC, not Nepal time. After 18:15 Nepal time it is already the next UTC day, so for a Nepal day use CAST(now() + 20700000 AS DATE) (see Nepal time). It may be written with or without ().

Example: Save the day a user logged in

Setup:

CREATE TABLE user_logins (user_id INT PRIMARY KEY, login_day DATE DEFAULT current_date);
INSERT INTO user_logins (user_id) VALUES (7);

SELECT user_id, login_day = current_date AS logged_in_today FROM user_logins;

Result:

(1 row)

Example: Today in UTC and on the Nepal clock

SELECT current_date AS utc_day,
       CAST(now() + 20700000 AS DATE) AS nepal_day,
       current_date = CAST(now() AS DATE) AS same_as_now;

Result:

(1 row)

current_time, localtime

Syntax:

current_time
current_time()
localtime
localtime()

The clock time of day now, on the session's clock (UTC unless set), as text: HH:MM:SS.mmm. localtime is the same function.

• Returns: STRING, 12 characters, like 05:15:00.000.
• NULL: never NULL.
• Notes: KDB has no TIME type, so this is text. To compare times of day, compare the text (current_time < '09:00:00.000') or use hour(now()). For a Nepal clock time use to_str(now() + 20700000, 'HH:mm:ss').

Example: Is the market session open right now (Nepal clock)?

SELECT current_time AS utc_time_of_day,
       to_str(now() + 20700000, 'HH:mm') AS nepal_clock,
       localtime = current_time AS localtime_is_the_same;

Result:

(1 row)

localtimestamp

Syntax:

localtimestamp
localtimestamp()
localtimestamp(digits)

The time now on the session's clock, the PostgreSQL name for a time without a zone. In a UTC session it is the same as now(); after SET TIME ZONE 'Asia/Kathmandu' it is 5 h 45 min later (the Nepal wall clock, still shown with Z).

• Returns: TIMESTAMP.
• NULL: never NULL.
• Notes: the same value for the whole statement, like now().

Example

SELECT localtimestamp = now() AS same_as_now, year(localtimestamp) >= 2026 AS year_ok;

Result:

(1 row)

transaction_timestamp, statement_timestamp, clock_timestamp

Syntax:

transaction_timestamp()
statement_timestamp()
clock_timestamp()

Three PostgreSQL names for the time now. In PostgreSQL they differ (start of the transaction, start of the statement, the clock right now). KDB gives the same time for all three: the time the statement started.

• Returns: TIMESTAMP.
• NULL: never NULL.
• Notes: clock_timestamp() in PostgreSQL moves on inside one statement; in KDB it does not, so you cannot use it to time a loop. Use it only for app code that came from PostgreSQL.

Example: All three are the time this statement started

SELECT transaction_timestamp() = statement_timestamp() AS same_1,
       statement_timestamp() = clock_timestamp() AS same_2,
       clock_timestamp() = now() AS same_as_now;

Result:

(1 row)

make_interval

Syntax:

make_interval(years, months, weeks, days, hours, minutes, seconds)

Builds an interval (a length of time) from numbers. Every argument may be left out from the right; what is left out is 0.

• Returns: INTERVAL. Add it to a time with +, take it off with -.
• NULL: any NULL argument gives NULL.
• Notes: a fraction is carried down, as PostgreSQL does: half a month becomes 15 days, half a day becomes 12 hours. Text that is a number ('3') is accepted, so a driver parameter works. INTERVAL '15 minutes' writes the same thing shorter.

Example: The candle 15 minutes after each market minute (sample data: sample)

SELECT time, time + make_interval(0, 0, 0, 0, 0, 15, 0) AS fifteen_minutes_later
FROM ochl_minutes
WHERE symbol = 'CITY'
ORDER BY time
LIMIT 3;

Result:

(3 rows)

Example: Years, months, weeks and a fraction

SELECT make_interval(1, 2, 0, 3) AS year_month_day,
       make_interval(0, 0, 2) AS two_weeks,
       make_interval(0, 0, 0, 0, 1, 30) AS ninety_minutes,
       make_interval(0, 0.5) AS half_a_month;

Result:

(1 row)

justify_days, justify_hours, justify_interval

Syntax:

justify_days(interval)
justify_hours(interval)
justify_interval(interval)

Write an interval in bigger units: justify_days turns every 30 days into a month, justify_hours turns every 24 hours into a day, and justify_interval does both and also makes the signs agree.

• Returns: INTERVAL.
• NULL: a NULL interval gives NULL. A value that is not an INTERVAL is an error.
• Notes: a month counts as 30 days here, as in PostgreSQL. So justify_days is for showing a length to a reader, not for exact day counts; use datediff for those.

Example: 45 days and 50 hours, written for a reader

SELECT justify_days(INTERVAL '45 days') AS in_months,
       justify_hours(INTERVAL '50 hours') AS in_days,
       justify_interval(INTERVAL '45 days 50 hours') AS both;

Result:

(1 row)

Example: How long a dividend book closure lasted

SELECT justify_interval(make_interval(0, 0, 0, 40, 30)) AS book_closure_length;

Result:

(1 row)