Results of 1 - 10 of about 32 for month (0.002 sec.)
- UNPIVOT Statement 13113
- The UNPIVOT statement allows multiple columns to be stacked into fewer columns. In the basic case, multiple columns are stacked into two columns: a NAME column (which contains the name of the source column) and a VALUE column (which contain
...⟩ LIMIT ⟨number_of_rows⟩; Example Data All examples use the dataset produced by the queries below: CREATE OR REPLACE TABLE monthly_sales (empid INTEGER, dept TEXT, Jan INTEGER, Feb INTEGER, Mar INTEGER, Apr INTEGER, May INTEGER, Jun INTEGER); INSERT I...NTO monthly_sales VALUES (1, 'electronics', 1, 2, 3, 4, 5, 6), (2, 'clothes', 10, 20, 30, 40, 50, 60), (3, 'cars', 100, 200, 300, 40...0, 500, 600); FROM monthly_sales; empid dept Jan Feb Mar Apr May Jun 1 electronics 1 2 3 4 5 6 2 clothes 10 20 30 40 50 60 3 cars 100 200 300 400 5... - https://man.mtool.work/duckdb/2.0/sql/statements/unpivot.html
- Hive Partitioning 12211
- Examples Read data from a Hive partitioned dataset: SELECT * FROM read_parquet('orders/*/*/*.parquet', hive_partitioning = true); Write a table to a Hive partitioned dataset: COPY orders TO 'orders' (FORMAT parquet, PARTITION_BY (year, month
...t use expressions. You can produce columns on the fly using the following syntax: COPY (SELECT *, year(timestamp) AS year, month(timestamp) AS month FROM services) TO 'test' (PARTITION_BY (year, month)); When reading, the partition columns are read fr...hive_partitioning parameter. FROM read_parquet('test/*/*/*.parquet', hive_partitioning = false); -- will not include year, month columns FROM read_parquet('test/*/*/*.parquet', hive_partitioning = true); -- will include year, month partition columns H...of the folder. Below is an example of a Hive partitioned file hierarchy. The files are partitioned on two keys ( year and month ). orders ├── year=2021 │ ├── month=1 │ │ ├── file1.parquet │ │ └── file2.parquet │ └── month=2 │ └── file3.parquet └── ye... - https://man.mtool.work/duckdb/2.0/data/partitioning/hive_partitioning.html
- Date Functions 10906
- This section describes functions and operators for examining and manipulating DATE values. Date Operators The table below shows the available mathematical operators for DATE types. Operator Description Example Result + addition of days (int
...f part . date_trunc(part, date) Truncate to specified precision . dayname(date) The (English) name of the weekday. days_in_month(date) The number of days in the month of the given date. extract(part from date) Get subfield from a date. greatest(date,...false otherwise. julian(date) Extract the Julian Day number from a date. last_day(date) The last day of the corresponding month in the date. least(date, date) The earlier of two dates. make_date(year, month, day) The date for the given parts. monthna...me(date) The (English) name of the month. strftime(date, format) Converts a date to a string according to the format string . time_bucket(bucket_width, date[, offs... - https://man.mtool.work/duckdb/2.0/sql/functions/date.html
- Interval Type 10568
- INTERVAL s represent periods of time that can be added to or subtracted from DATE , TIMESTAMP , TIMESTAMPTZ , or TIME values. Name Description INTERVAL Period of time An INTERVAL can be constructed by providing amounts together with units.
...Units that aren't months , days , or microseconds are converted to equivalent amounts in the next smaller of these three basis units. SELECT INTER...VAL 1 YEAR, -- single unit using YEAR keyword; stored as 12 months INTERVAL (random() * 10) YEAR, -- parentheses necessary for variable amounts; -- stored as integer number of months INTER...VAL '1 month 1 day', -- string type necessary for multiple units; stored as (1 month, 1 day) '16 months'::INTERVAL, -- string cast supp... - https://man.mtool.work/duckdb/2.0/sql/data_types/interval.html
- Partitioned Writes 10439
- Examples Write a table to a Hive partitioned dataset of Parquet files: COPY orders TO 'orders' (FORMAT parquet, PARTITION_BY (year, month)); Write a table to a Hive partitioned dataset of CSV files, allowing overwrites: COPY orders TO 'orde
...rs' (FORMAT csv, PARTITION_BY (year, month), OVERWRITE_OR_IGNORE); Write a table to a Hive partitioned dataset of GZIP-compressed CSV files, setting explicit data fi...les' extension: COPY orders TO 'orders' (FORMAT csv, PARTITION_BY (year, month), COMPRESSION gzip, FILE_EXTENSION 'csv.gz'); Partitioned Writes When the PARTITION_BY clause is specified for the COPY st...en in-order in the file hierarchy. Currently, one file is written per thread to each directory. orders ├── year=2021 │ ├── month=1 │ │ ├── data_1.parquet │ │ └── data_2.parquet │ └── month=2 │ └── data_1.parquet └── year=2022 ├── month=11 │ ├── data_1... - https://man.mtool.work/duckdb/2.0/data/partitioning/partitioned_writes.html
- Date Format Functions 8876
- The strftime and strptime functions can be used to convert between DATE / TIMESTAMP values and strings. This is often required when parsing CSV files, displaying output to the user or transferring information between programs. Because there
...S x; Invalid Input Error: Could not parse string "02/50/1992" according to format specifier "%d/%m/%Y" 02/50/1992 ^ Error: Month out of range, expected a value between 1 and 12 To return NULL on failure, use the try_strptime function : SELECT try_strp...er Description Example %a Abbreviated weekday name. Sun, Mon, ... %A Full weekday name. Sunday, Monday, ... %b Abbreviated month name. Jan, Feb, ..., Dec %B Full month name. January, February, ... %c ISO date and time representation 1992-03-02 10:30:2...0 %d Day of the month as a zero-padded decimal. 01, 02, ..., 31 %-d Day of the month as a decimal number. 1, 2, ..., 30 %f Microsecond as a deci... - https://man.mtool.work/duckdb/2.0/sql/functions/dateformat.html
- Date Part Functions 8554
- The date_part , date_trunc and date_diff functions can be used to extract or manipulate parts of temporal types such as TIMESTAMP , TIMESTAMPTZ , DATE and INTERVAL . The parts to be extracted or manipulated are specified by one of the strin
...Specifier Description Synonyms Example century Gregorian century cent , centuries , c 21 day Gregorian day days , d , dayofmonth 3 decade Gregorian decade dec , decades , decs 202 hour Hours hr , hours , hrs , h 11 microseconds Sub-minute microseconds...-minute milliseconds millisecond , ms , msec , msecs , msecond , mseconds 44123 minute Minutes min , minutes , mins , m 59 month Gregorian month mon , months , mons 8 quarter Quarter of the year (1-4) quarters 3 second Seconds sec , seconds , secs , s...e are dedicated extraction functions to get certain subfields: Name Description century(date) Century. day(date) Day. dayofmonth(date) Day (synonym). dayofweek(date) Numeric weekday (Sunday = 0, Saturday = 6). dayofyear(date) Day of the year (starts f... - https://man.mtool.work/duckdb/2.0/sql/functions/datepart.html
- Interval Functions 8361
- This section describes functions and operators for examining and manipulating INTERVAL values. Interval Operators The table below shows the available mathematical operators for INTERVAL types. Operator Description Example Result + Addition
...m interval. to_milliseconds(integer) Construct a millisecond interval. to_minutes(integer) Construct a minute interval. to_months(integer) Construct a month interval. to_quarters(integer ) Construct an interval of integer quarters. to_seconds(integer)...). See INTERVAL for the sometimes surprising rules governing this extraction. | | Example | date_part('year', INTERVAL '14 months') | | Result | 1 | datepart(part, interval) | Description | Alias of date_part . | | Example | datepart('year', INTERVAL...'14 months') | | Result | 1 | extract(part FROM interval) | Description | Alias of date_part . | | Example | extract('month' FROM IN... - https://man.mtool.work/duckdb/2.0/sql/functions/interval.html
- Pivot Internals 8312
- PIVOT Pivoting is implemented as a combination of SQL query re-writing and a dedicated PhysicalPivot operator for higher performance. Each PIVOT is implemented as set of aggregations into lists and then the dedicated PhysicalPivot operator
...s. If dynamically unpivoting, the COLUMNS expression is evaluated first to calculate the column list. For example: UNPIVOT monthly_sales ON jan, feb, mar, apr, may, jun INTO NAME month VALUE sales; is translated into: SELECT empid, dept, unnest(['jan'..., 'feb', 'mar', 'apr', 'may', 'jun']) AS month, unnest(["jan", "feb", "mar", "apr", "may", "jun"]) AS sales FROM monthly_sales; Note the single quotes to build a list of...text strings to populate month , and the double quotes to pull the column values for use in sales . This produces the same result as the initial example:... - https://man.mtool.work/duckdb/2.0/internals/pivot.html
- Timestamp Functions 8087
- This section describes functions and operators for examining and manipulating TIMESTAMP values . See also the related TIMESTAMPTZ functions . Timestamp Operators The table below shows the available mathematical operators for TIMESTAMP types
...false otherwise. julian(timestamp) Extract the Julian Day number from a timestamp. last_day(timestamp) The last day of the month. least(timestamp, timestamp) The earlier of two timestamps. make_timestamp(bigint, bigint, bigint, bigint, bigint, double)...econds since the epoch to a timestamp. make_timestamp_ns(nanoseconds) Converts nanoseconds since the epoch to a timestamp. monthname(timestamp) The (English) name of the month. strftime(timestamp, format) Converts timestamp to string according to the...to a grid of width bucket_width . The grid is anchored at 2000-01-01 00:00:00[ + offset] when bucket_width is a number of months or coarser units, else 2000-01-03 00:00:00[ + offset] . Note that 2000-01-03 is a Monday. time_bucket(bucket_width, times... - https://man.mtool.work/duckdb/2.0/sql/functions/timestamp.html
DuckDB 2.0 Documentation / man.mtool.work