- Documentation
- Installation
- Data Import
- Client APIs
- SQL
- Introduction
- Statements
- Overview
- Select
- Insert
- Delete
- Update
- Create Schema
- Create Table
- Create View
- Create Sequence
- Create Macro
- Drop
- Alter Table
- Copy
- Export
- Data Types
- Expressions
- Functions
- Overview
- Numeric Functions
- Text Functions
- Pattern Matching
- Date Functions
- Timestamp Functions
- Time Functions
- Interval Functions
- Date Formats
- Date Parts
- Blob Functions
- Nested Functions
- Indexes
- Aggregates
- Window Functions
- Samples
- Pragmas
- Full Text Search
- Development
- Why DuckDB
- FAQ
- Code of Conduct
- Live Demo
This section describes functions and operators for examining and manipulating TIMESTAMP
values.
Timestamp Operators
The table below shows the available mathematical operators for TIMESTAMP
types.
Operator | Description | Example | Result |
---|---|---|---|
+ |
addition of an INTERVAL |
TIMESTAMP '1992-03-22 01:02:03' + INTERVAL 5 DAY |
1992-03-27 01:02:03 |
- |
subtraction of TIMESTAMP s |
TIMESTAMP '1992-03-27' - TIMESTAMP '1992-03-22' |
INTERVAL 5 DAY |
- |
subtraction of an INTERVAL |
TIMESTAMP '1992-03-27 01:02:03' - INTERVAL 5 DAY' |
1992-03-22 01:02:03 |
Timestamp Functions
The table below shows the available scalar functions for TIMESTAMP
types.
Function | Description | Example | Result |
---|---|---|---|
age( timestamp , timestamp ) |
Subtract arguments, resulting in the time difference between the two timestamps | age(TIMESTAMP '2001-04-10', TIMESTAMP '1992-09-20' ) |
8 years 6 mons 20 days |
age( timestamp ) |
Subtract from current_date | age(TIMESTAMP '1992-09-20') |
26 years 9 mons 9 days |
century( timestamp ) |
Extracts the century of a timestamp | century(TIMESTAMP '1992-03-22') |
20 |
current_timestamp |
Current date and time (start of current transaction) | ||
date_diff( part , startdate , enddate ) |
The number of partition boundaries between the timestamps | date_diff('hour', TIMESTAMP '1992-09-30 23:59:59', TIMESTAMP '1992-10-01') |
1 |
date_part( part , timestamp ) |
Get subfield (equivalent to extract) | date_part('minute', TIMESTAMP '1992-09-20 20:38:40') |
38 |
date_trunc( part , timestamp ) |
Truncate to specified precision | date_trunc('hour', TIMESTAMP '1992-09-20 20:38:40') |
1992-09-20 20:00:00 |
day( timestamp ) |
Extracts the day of a timestamp | dayname(TIMESTAMP '1992-03-22') |
Sunday |
dayname( timestamp ) |
The (English) name of the weekday | day(TIMESTAMP '1992-03-22') |
22 |
dayofweek( timestamp ) |
Extracts the day of the week of a timestamp (0-6, 0 = Sunday, 6 = Saturday) | dayofweek(TIMESTAMP '1992-03-22') |
0 |
dayofyear( timestamp ) |
Extracts the day of the year of a timestamp (1-366) | dayofyear(TIMESTAMP '1992-03-22') |
81 |
decade( timestamp ) |
Extracts the decade of a timestamp | decade(TIMESTAMP '1992-03-22') |
199 |
epoch( timestamp ) |
Extracts the epoch of a timestamp in seconds | epoch(TIMESTAMP '1992-03-22') |
701222400 |
epoch_ms(ms) |
Converts ms since epoch to a timestamp | epoch_ms(701222400000) |
1992-03-22 00:00:00 |
extract( field from timestamp ) |
Get subfield from a timestamp | extract('hour' FROM TIMESTAMP '1992-09-20 20:38:48') |
20 |
greatest( timestamp , timestamp ) |
The later of two timestamps | least(TIMESTAMP '1992-09-20 20:38:48', TIMESTAMP '1992-03-22 01:02:03.1234') |
1992-09-20 20:38:48 |
hour( timestamp ) |
Extracts the hour component of a timestamp | hour(TIMESTAMP '1992-03-22 01:02:03.1234') |
1 |
isodow( timestamp ) |
Extracts the ISO day of the week of a timestamp (1-7, 1 = Monday, 7 = Sunday) | isodow(TIMESTAMP '1992-03-22') |
7 |
last_day( timestamp ) |
The last day of the month. | last_day(TIMESTAMP '1992-03-22 01:02:03.1234') |
1992-03-31 |
least( timestamp , timestamp ) |
The earlier of two timestamps | least(TIMESTAMP '1992-09-20 20:38:48', TIMESTAMP '1992-03-22 01:02:03.1234') |
1992-03-22 01:02:03.1234 |
microsecond( timestamp ) |
Extracts the sub-minute component of a timestamp in microseconds | microsecond(TIMESTAMP '1992-03-22 01:02:03.1234') |
3123400 |
millisecond( timestamp ) |
Extracts the sub-minute component of a timestamp in milliseconds | millisecond(TIMESTAMP '1992-03-22 01:02:03.1234') |
3123 |
millenium( timestamp ) |
Extracts the millenium of a timestamp | millenium(TIMESTAMP '1992-03-22') |
2 |
minute( timestamp ) |
Extracts the minute component of a timestamp | minute(TIMESTAMP '1992-03-22 01:02:03.1234') |
2 |
month( timestamp ) |
Extracts the month of a timestamp | month(TIMESTAMP '1992-03-22') |
3 |
monthname( timestamp ) |
The (English) name of the month. | monthname(TIMESTAMP '1992-09-20') |
March |
now() |
Current date and time (start of current transaction) | ||
quarter( timestamp ) |
Extracts the quarter of a timestamp | quarter(TIMESTAMP '1992-03-22') |
1 |
second( timestamp ) |
Extracts the second component of a timestamp | second(TIMESTAMP '1992-03-22 01:02:03.1234') |
3 |
strftime(timestamp, format) |
Converts timestamp to string according to the format string | strftime(timestamp '1992-01-01 20:38:40', '%a, %-d %B %Y - %I:%M:%S %p') |
Wed, 1 January 1992 - 08:38:40 PM |
strptime(text, format) |
Converts string to timestamp according to the format string | strptime('Wed, 1 January 1992 - 08:38:40 PM', '%a, %-d %B %Y - %I:%M:%S %p') |
1992-01-01 20:38:40 |
to_timestamp(sec) |
Converts sec since epoch to a timestamp | to_timestamp(701222400) |
1992-03-22 00:00:00 |
week( timestamp ) |
Extracts the week number of a timestamp (1-53) | week(TIMESTAMP '1992-03-22') |
12 |
year( timestamp ) |
Extracts the year of a timestamp | year(TIMESTAMP '1992-03-22') |
1992 |
Timestamp Table Functions
The table below shows the available scalar functions for TIMESTAMP
types.
Function | Description | Example |
---|---|---|
generate_series( timestamp , timestamp , interval ) |
Generate a table of timestamps in the closed range, stepping by the interval | range(TIMESTAMP '2001-04-10', TIMESTAMP '2001-04-11', INTERVAL 30 MINUTE) |
range( timestamp , timestamp , interval ) |
Generate a table of timestamps in the half open range, stepping by the interval | range(TIMESTAMP '2001-04-10', TIMESTAMP '2001-04-11', INTERVAL 30 MINUTE) |