New datestyle(s)

Anyway, this is in response to a really old request of mine to easily
and automatically get a datestyle for a different DBMS as part of an
ongoing port process
(http://archives.postgresql.org/pgsql-sql/2008-05/msg00048.php).

I patched this a while ago and I finally got a chance to test it.
Overall, it was pretty easy once I figured out how existing datestyles
were coded (lots of grep'ing was involved). So, I figured I'd share
what I did and also share the source patch in case anyone would either
like a Sybase/SQL Server datestyle or a model on how to add your own
custom datestyles.

You need to edit the following files:
src/backend/commands/variable.c - Add in your new datestyle to the
list to allow it to be a valid option for "SET datestyle TO <x>"
src/backend/utils/adt/datetime.c - Define your output format. A little
hack-ish but pretty straightforward overall.
src/include/miscadmin.h - Define your new datestyle.
src/bin/psql/tab-complete.c - Not necessary to add it, but having it
in the list of tab completions for datestyle is nice.

I don't use the ecpg interface, so I didn't bother patching that. It
seems like it would be analogous to what's been done already.

Peter

Index: src/backend/commands/variable.c
===================================================================
RCS file: /s/postgresql-8.3.3/src/CVSROOT/postgresql-8.3.3/src/backend/commands/variable.c,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- src/backend/commands/variable.c 3 Sep 2008 18:30:21 -0000 1.1
+++ src/backend/commands/variable.c 3 Sep 2008 18:52:50 -0000 1.2
@@ -100,6 +100,13 @@
if (!have_order)
newDateOrder = DATEORDER_DMY;
}
+ else if (pg_strcasecmp(tok, "SYBASE") == 0)
+ {
+ if (have_style && newDateStyle != USE_SYBASE_DATES)
+ ok = false; /* conflicting styles */
+ newDateStyle = USE_SYBASE_DATES;
+ have_style = true;
+ }
else if (pg_strcasecmp(tok, "YMD") == 0)
{
if (have_order && newDateOrder != DATEORDER_YMD)
@@ -200,6 +207,9 @@
case USE_GERMAN_DATES:
strcpy(result, "German");
break;
+ case USE_SYBASE_DATES:
+ strcpy(result, "Sybase");
+ break;
default:
strcpy(result, "Postgres");
break;
Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /s/postgresql-8.3.3/src/CVSROOT/postgresql-8.3.3/src/backend/utils/adt/datetime.c,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- src/backend/utils/adt/datetime.c 3 Sep 2008 18:30:26 -0000 1.1
+++ src/backend/utils/adt/datetime.c 3 Sep 2008 18:53:29 -0000 1.2
@@ -3233,6 +3233,15 @@
sprintf(str + 5, ".%04d %s", -(tm->tm_year - 1), "BC");
break;

+ case USE_SYBASE_DATES:
+ /* Sybase date format */
+ sprintf(str, "%s %2d", months[tm->tm_mon - 1], tm->tm_mday);
+ if (tm->tm_year > 0)
+ sprintf(str + 6, " %04d", tm->tm_year);
+ else
+ sprintf(str + 6, " %04d %s", -(tm->tm_year - 1), "BC");
+ break;
+
case USE_POSTGRES_DATES:
default:
/* traditional date-only style for Postgres */
@@ -3302,6 +3311,8 @@
EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn,
int style, char *str)
{
int day;
+ bool meridian;
+ int temp_hour;

/*
* Why are we checking only the month field? Change this to an assert...
@@ -3452,6 +3463,32 @@
sprintf(str + strlen(str), " BC");
break;

+ case USE_SYBASE_DATES:
+ /* Sybase date format */
+ meridian = true; // true = AM, false = PM
+ temp_hour = tm->tm_hour;
+
+ if (temp_hour < 12)
+ {
+ meridian = true;
+ if (temp_hour == 0) temp_hour = 12;
+ }
+ else
+ {
+ meridian = false;
+ if (temp_hour > 12) temp_hour -= 12;
+ }
+
+ sprintf(str, "%s %2d %04d %2d:%02d%s",
+ months[tm->tm_mon - 1], tm->tm_mday,
+ (tm->tm_year > 0) ? tm->tm_year : -(tm->tm_year - 1),
+ temp_hour, tm->tm_min,
+ (meridian) ? "AM" : "PM");
+
+ if (tm->tm_year <= 0)
+ sprintf(str + strlen(str), " BC");
+ break;
+
case USE_POSTGRES_DATES:
default:
/* Backward-compatible with traditional Postgres abstime dates */
Index: src/include/miscadmin.h
===================================================================
RCS file: /s/postgresql-8.3.3/src/CVSROOT/postgresql-8.3.3/src/include/miscadmin.h,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- src/include/miscadmin.h 3 Sep 2008 18:30:34 -0000 1.1
+++ src/include/miscadmin.h 3 Sep 2008 18:53:45 -0000 1.2
@@ -183,6 +183,7 @@
#define USE_SQL_DATES 2
#define USE_GERMAN_DATES 3
#define USE_XSD_DATES 4
+#define USE_SYBASE_DATES 5

/* valid DateOrder values */
#define DATEORDER_YMD 0
Index: src/bin/psql/tab-complete.c
===================================================================
RCS file: /s/postgresql-8.3.3/src/CVSROOT/postgresql-8.3.3/src/bin/psql/tab-complete.c,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- src/bin/psql/tab-complete.c 3 Sep 2008 18:30:34 -0000 1.1
+++ src/bin/psql/tab-complete.c 3 Sep 2008 18:53:42 -0000 1.2
@@ -1888,7 +1888,7 @@
if (pg_strcasecmp(prev2_wd, "DateStyle") == 0)
{
static const char *const my_list[] =
- {"ISO", "SQL", "Postgres", "German",
+ {"ISO", "SQL", "Postgres", "German", "Sybase",
"YMD", "DMY", "MDY",
"US", "European", "NonEuropean",
"DEFAULT", NULL};