Skip to content

Commit 1c5aec6

Browse files
bmomjianbmomjian
committed
I finish devel. of Oracle compatible DateTime routines TO_CHAR(),
TO_DATE() and PgSQL extension FROM_CHAR(). TO_CHAR() routine allow formating text output with a datetime values: SELECT TO_CHAR('now'::datetime, '"Now is: "HH24:MI:SS'); to_char ---------------- Now is: 21:04:10 FROM_CHAR() routine allow convert text to a datetime: SELECT FROM_CHAR('September 1999 10:20:30', 'FMMonth YYYY HH:MI:SS'); from_char ----------------------------- Wed Sep 01 10:20:30 1999 CEST TO_DATE() is equal with FROM_CHAR(), but output a Date only: SELECT TO_DATE('September 1999 10:20:30', 'FMMonth YYYY HH:MI:SS'); to_date ---------- 09-01-1999 In attache is compressed dir for the contrib. All is prepared, but I'am not sure if Makefile is good (probably yes). Comments & suggestions ? Thomas, thank you for your good advices. Karel ------------------------------------------------------------------------------ Karel Zak <zakkr@zf.jcu.cz> http://home.zf.jcu.cz/~zakkr/
1 parent 1f747c6 commit 1c5aec6

File tree

10 files changed

+1878
-0
lines changed

10 files changed

+1878
-0
lines changed

‎contrib/README

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,14 @@ array -
1010
Array iterator functions
1111
by Massimo Dal Zotto <dz@cs.unitn.it>
1212

13+
bit -
14+
Bit type
15+
by Adriaan Joubert <a.joubert@albourne.com>
16+
17+
dateformat -
18+
Date Formatting to/from character strings
19+
by Karel Zak - Zakkr <zakkr@zf.jcu.cz>
20+
1321
datetime -
1422
Date & time functions
1523
by Massimo Dal Zotto <dz@cs.unitn.it>

‎contrib/dateformat/Makefile

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,71 @@
1+
#-------------------------------------------------------------------------
2+
#
3+
# Makefile --
4+
#
5+
# Makefile for TO-FROM_CHAR module.
6+
#
7+
#-------------------------------------------------------------------------
8+
9+
PGDIR = ../..
10+
SRCDIR = $(PGDIR)/src
11+
12+
include $(SRCDIR)/Makefile.global
13+
14+
INCLUDE_OPT = -I ./ \
15+
-I $(SRCDIR)/ \
16+
-I $(SRCDIR)/include \
17+
-I $(SRCDIR)/port/$(PORTNAME)
18+
19+
CFLAGS += $(INCLUDE_OPT) $(CFLAGS_SL)
20+
21+
MODNAME = to-from_char
22+
23+
SQLDEFS = $(MODNAME).sql
24+
25+
MODULE = $(MODNAME)$(DLSUFFIX)
26+
27+
MODDIR = $(LIBDIR)/modules
28+
29+
SQLDIR = $(LIBDIR)/sql
30+
31+
all: module sql
32+
33+
module: $(MODULE)
34+
35+
sql: $(SQLDEFS)
36+
37+
install: $(MODULE) $(SQLDEFS) $(MODDIR) $(SQLDIR)
38+
cp -p $(MODULE) $(MODDIR)/
39+
strip $(MODDIR)/$(MODULE)
40+
cp -p $(SQLDEFS) $(SQLDIR)/
41+
42+
install-doc:
43+
if [ -d "$(DOCDIR)" ]; then \
44+
cp -p *.doc $(DOCDIR); \
45+
else \
46+
cp -p *.doc $(SQLDIR); \
47+
fi
48+
49+
$(MODDIR):
50+
mkdir -p $@
51+
52+
$(SQLDIR):
53+
mkdir -p $@
54+
55+
%.sql: %.sql.in
56+
sed "s|MODULE_PATHNAME|$(MODDIR)/$(MODULE)|" < $< > $@
57+
58+
.SUFFIXES: $(DLSUFFIX)
59+
60+
%$(DLSUFFIX): %.c
61+
$(CC) $(CFLAGS) -shared -o $@ $<
62+
63+
depend dep:
64+
$(CC) -MM $(INCLUDE_OPT) *.c >depend
65+
66+
clean:
67+
rm -f *~ $(MODULE) $(MODNAME).sql
68+
69+
ifeq (depend,$(wildcard depend))
70+
include depend
71+
endif

‎contrib/dateformat/test/Makefile

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
2+
PROGRAM = rand_datetime
3+
4+
OBJECTS = rand_datetime.o
5+
6+
CFLAGS = -Wall -fpic -O3
7+
CC = gcc
8+
RM = rm -f
9+
LIBS =
10+
INCLUDE =
11+
12+
COMPILE = $(CC) $(CPPFLAGS) $(CFLAGS) $(INCLUDE)
13+
LINK = $(CC) $(CFLAGS) -o $@ $(LIBS)
14+
15+
16+
all: $(PROGRAM)
17+
18+
$(PROGRAM): $(OBJECTS)
19+
$(LINK) $(OBJECTS)
20+
21+
.c.o: $<
22+
$(COMPILE) -c $<
23+
24+
clean:
25+
$(RM) -f *~ $(OBJECTS) $(PROGRAM)

‎contrib/dateformat/test/README

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,33 @@
1+
2+
TO/FROM CHAR tests
3+
~~~~~~~~~~~~~~~~~~
4+
5+
* rand_datetime
6+
7+
The program 'rand_datetime' output a random datetime strings
8+
(with yaer range 0..9999), you can use this for datetime testing.
9+
10+
You can usage this (example) for table filling.
11+
12+
Usage:
13+
14+
./rand_datetime <randfile> <num> <prefix> <postfix>
15+
16+
Example:
17+
18+
./rand_datetime /dev/urandom 2 "INSERT INTO tab VALUES('" "'::datetime);"
19+
20+
INSERT INTO tab VALUES('Sat 27 Jul 13:08:57 19618'::datetime);
21+
INSERT INTO tab VALUES('Wed 25 Aug 20:31:50 27450'::datetime);
22+
23+
* regress
24+
25+
psql < regress.sql (all answers, must be TRUE, for Posgres
26+
datestyle)
27+
28+
29+
--> TO_DATE() is simular as FROM_CHAR(), but convert full datetime
30+
to date ==> needn't test (?).
31+
32+
33+

‎contrib/dateformat/test/rand_datetime.c

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,71 @@
1+
2+
#include <stdio.h>
3+
#include <errno.h>
4+
#include <ctype.h>
5+
#include <stdlib.h>
6+
7+
8+
char *month[] = {
9+
"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec",NULL
10+
};
11+
12+
char *day[] = { "Sun","Mon","Tue","Wed","Thu","Fri","Sat", NULL };
13+
14+
int num(FILE *f, int min, int max)
15+
{
16+
int x, y, one;
17+
18+
one = x = fgetc(f);
19+
20+
21+
if (x < min)
22+
x = min;
23+
else if (x > max) {
24+
while(x > max)
25+
x /= 2;
26+
return x;
27+
}
28+
29+
do {
30+
y = fgetc(f);
31+
if ((x+y) > max)
32+
return x;
33+
x += y;
34+
} while(--one > 0);
35+
36+
return x;
37+
}
38+
39+
int main(int argc, char **argv)
40+
{
41+
FILE *f;
42+
int count;
43+
44+
if (argc < 5) {
45+
printf("\nUsage: %s <randfile> <num> <prefix> <postfix>\n", argv[0]);
46+
printf("\n(C) Karel Zak - Zakkr 1999\n\n");
47+
exit(1);
48+
}
49+
50+
if ((f = fopen(argv[1], "r")) == NULL) {
51+
perror(argv[1]);
52+
exit(1);
53+
}
54+
55+
count = atoi(argv[2]);
56+
57+
for(; count > 0; --count) {
58+
fprintf(stdout, "%s%s %02d %s %02d:%02d:%02d %d%s\n",
59+
argv[3],
60+
day[ num(f, 0, 6) ],
61+
num(f, 1, 28),
62+
month[ num(f, 0, 11) ],
63+
num(f, 0, 23),
64+
num(f, 0, 59),
65+
num(f, 0, 59),
66+
num(f, 0, 9999),
67+
argv[4]
68+
);
69+
}
70+
exit(0);
71+
}

‎contrib/dateformat/test/regress.sql

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,58 @@
1+
2+
---
3+
--- Postgres DateStyle needs all tests which parsing 'now'::datetime string
4+
---
5+
SET DATESTYLE TO 'Postgres';
6+
7+
8+
SELECT 'now'::datetime =
9+
TO_CHAR('now'::datetime, 'Dy Mon DD HH24:MI:SS YYYY')::datetime
10+
as "Now vs. to_char";
11+
12+
13+
SELECT 'now'::datetime =
14+
FROM_CHAR('now'::datetime, 'Dy Mon DD HH24:MI:SS YYYY')
15+
as "Now vs. from_char";
16+
17+
18+
SELECT FROM_CHAR('now'::datetime, 'Dy Mon DD HH24:MI:SS YYYY') =
19+
TO_CHAR('now'::datetime, 'Dy Mon DD HH24:MI:SS YYYY')::datetime
20+
as "From_char vs. To_char";
21+
22+
23+
SELECT 'now'::datetime =
24+
FROM_CHAR(
25+
TO_CHAR('now'::datetime, '"Time: "HH24-MI-SS" Date: "Dy DD Mon YYYY'),
26+
'"Time: "HH24-MI-SS" Date: "Dy DD Mon YYYY'
27+
)
28+
as "High from/to char test";
29+
30+
31+
SELECT TO_CHAR('now'::datetime, 'SSSS')::int =
32+
TO_CHAR('now'::datetime, 'HH24')::int * 3600 +
33+
TO_CHAR('now'::datetime, 'MI')::int * 60 +
34+
TO_CHAR('now'::datetime, 'SS')::int
35+
as "SSSS test";
36+
37+
38+
SELECT TO_CHAR('now'::datetime, 'WW')::int =
39+
(TO_CHAR('now'::datetime, 'DDD')::int -
40+
TO_CHAR('now'::datetime, 'D')::int + 7) / 7
41+
as "Week test";
42+
43+
44+
SELECT TO_CHAR('now'::datetime, 'Q')::int =
45+
TO_CHAR('now'::datetime, 'MM')::int / 3 + 1
46+
as "Quartal test";
47+
48+
49+
SELECT TO_CHAR('now'::datetime, 'DDD')::int =
50+
(TO_CHAR('now'::datetime, 'WW')::int * 7) -
51+
(7 - TO_CHAR('now'::datetime, 'D')::int) +
52+
(7 - TO_CHAR(('01-Jan-'||
53+
TO_CHAR('now'::datetime,'YYYY'))::datetime,'D')::int)
54+
+1
55+
as "Week and day test";
56+
57+
58+

0 commit comments

Comments
 (0)