# name: test/sql/function/timestamp/test_strftime_timestamp.test
# description: Test all strftime % codes with the date type
# group: [timestamp]

statement ok
SET default_null_order='nulls_first';

statement ok
PRAGMA enable_verification

statement ok
CREATE TABLE timestamps(d TIMESTAMP);
INSERT INTO timestamps VALUES 
	('1992-01-01 01:20:30'), 
	('1993-03-20 23:50:01.123'), 
	('2020-08-09 12:01:55.123456'), 
	('2020-08-10 10:10:10.123456789'), 
	(NULL);

# %a: Abbreviated weekday name
query I
SELECT strftime(d, '%a') FROM timestamps ORDER BY d;
----
NULL
Wed
Sat
Sun
Mon

# %A: Full weekday name
query I
SELECT strftime(d, '%A') FROM timestamps ORDER BY d;
----
NULL
Wednesday
Saturday
Sunday
Monday

# %w - Weekday as a decimal number.
query I
SELECT strftime(d, '%w') FROM timestamps ORDER BY d;
----
NULL
3
6
0
1

# %u - ISO Weekday as a decimal number.
query I
SELECT strftime(d, '%u') FROM timestamps ORDER BY d;
----
NULL
3
6
7
1

# %d - Day of the month as a zero-padded decimal.
query I
SELECT strftime(d, '%d') FROM timestamps ORDER BY d;
----
NULL
01
20
09
10

# %-d - Day of the month as a decimal number.
query I
SELECT strftime(d, '%-d') FROM timestamps ORDER BY d;
----
NULL
1
20
9
10

# %b - Abbreviated month name.
query I
SELECT strftime(d, '%b') FROM timestamps ORDER BY d;
----
NULL
Jan
Mar
Aug
Aug

# %h - alias for %b
query I
SELECT strftime(d, '%h') FROM timestamps ORDER BY d;
----
NULL
Jan
Mar
Aug
Aug

# %B - Full month name
query I
SELECT strftime(d, '%B') FROM timestamps ORDER BY d;
----
NULL
January
March
August
August

# %m - Month as a zero-padded decimal number
query I
SELECT strftime(d, '%m') FROM timestamps ORDER BY d;
----
NULL
01
03
08
08

# %-m - Month as a decimal number. (1, 2, ..., 12)
query I
SELECT strftime(d, '%-m') FROM timestamps ORDER BY d;
----
NULL
1
3
8
8

# %y - Year without century as a zero-padded decimal number.
query I
SELECT strftime(d, '%y') FROM timestamps ORDER BY d;
----
NULL
92
93
20
20

# %-y - Year without century as a decimal number.
query I
SELECT strftime(d, '%-y') FROM timestamps ORDER BY d;
----
NULL
92
93
20
20

query I
SELECT strftime(DATE '2001-01-01', '%-y')
----
1

# %Y - Year with century as a decimal number.
query I
SELECT strftime(d, '%Y') FROM timestamps ORDER BY d;
----
NULL
1992
1993
2020
2020

# %G - ISO Year with century as a decimal number.
query I
SELECT strftime(d, '%G') FROM timestamps ORDER BY d;
----
NULL
1992
1993
2020
2020

# %H - Hour (24-hour clock) as a zero-padded decimal number.
query I
SELECT strftime(d, '%H') FROM timestamps ORDER BY d;
----
NULL
01
23
12
10

# %-H - Hour (24-hour clock) as a decimal number. (0, 1, ..., 23)
query I
SELECT strftime(d, '%-H') FROM timestamps ORDER BY d;
----
NULL
1
23
12
10

# %I - Hour (12-hour clock) as a zero-padded decimal number.
query I
SELECT strftime(d, '%I') FROM timestamps ORDER BY d;
----
NULL
01
11
12
10

# %-I - Hour (12-hour clock) as a decimal number. (1, 2, ... 12)
query I
SELECT strftime(d, '%-I') FROM timestamps ORDER BY d;
----
NULL
1
11
12
10

# %p - Locale’s AM or PM.
query I
SELECT strftime(d, '%p') FROM timestamps ORDER BY d;
----
NULL
AM
PM
PM
AM

# %M - Minute as a zero-padded decimal number.
query I
SELECT strftime(d, '%M') FROM timestamps ORDER BY d;
----
NULL
20
50
01
10

# %-M - Minute as a decimal number. (0, 1, ..., 59)
query I
SELECT strftime(d, '%-M') FROM timestamps ORDER BY d;
----
NULL
20
50
1
10

# %S - Second as a zero-padded decimal number.
query I
SELECT strftime(d, '%S') FROM timestamps ORDER BY d;
----
NULL
30
01
55
10

# %-S - Second as a decimal number. (0, 1, ..., 59)
query I
SELECT strftime(d, '%-S') FROM timestamps ORDER BY d;
----
NULL
30
1
55
10

# %f - Microsecond as a decimal number, zero-padded on the left.
query I
SELECT strftime(d, '%f') FROM timestamps ORDER BY d;
----
NULL
000000
123000
123456
123456

# %g - Millisecond as a decimal number, zero-padded on the left.
query I
SELECT strftime(d, '%g') 
FROM timestamps 
ORDER BY d;
----
NULL
000
123
123
123

# %n - Nanosecond as a decimal number, zero-padded on the left.
query I
SELECT strftime(d, '%n') 
FROM timestamps 
ORDER BY d;
----
NULL
000000000
123000000
123456000
123456000

# %z - UTC offset in the form +HHMM or -HHMM.
query I
SELECT strftime(d, '%z') FROM timestamps ORDER BY d;
----
NULL
+00
+00
+00
+00

# %Z - Time zone name.
query I
SELECT strftime(d, '%Z') FROM timestamps ORDER BY d;
----
NULL
(empty)
(empty)
(empty)
(empty)

# %j - Day of the year as a zero-padded decimal number.
query I
SELECT strftime(d, '%j') FROM timestamps ORDER BY d;
----
NULL
001
079
222
223

# %-j - Day of the year as a decimal number. (1, 2, ..., 366)
query I
SELECT strftime(d, '%-j') FROM timestamps ORDER BY d;
----
NULL
1
79
222
223


# %U - Week number of the year (Sunday as the first day of the week).
query I
SELECT strftime(d, '%U') FROM timestamps ORDER BY d;
----
NULL
00
11
32
32

# %W - Week number of the year (Monday as the first day of the week).
query I
SELECT strftime(d, '%W') FROM timestamps ORDER BY d;
----
NULL
00
11
31
32

# %V - ISO Week number of the year (Week 1 contains Jan 4).
query I
SELECT strftime(d, '%V') FROM timestamps ORDER BY d;
----
NULL
01
11
32
33

# %c - Locale’s appropriate date and time representation.
query I
SELECT strftime(d, '%c') FROM timestamps ORDER BY d;
----
NULL
1992-01-01 01:20:30
1993-03-20 23:50:01
2020-08-09 12:01:55
2020-08-10 10:10:10

# %x - Locale’s appropriate date representation.
query I
SELECT strftime(d, '%x') FROM timestamps ORDER BY d;
----
NULL
1992-01-01
1993-03-20
2020-08-09
2020-08-10

# X - Locale’s appropriate time representation.
query I
SELECT strftime(d, '%X') FROM timestamps ORDER BY d;
----
NULL
01:20:30
23:50:01
12:01:55
10:10:10