New datestyle(s) - Mailing list pgsql-sql

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
*constmy_list[] =
 
-            {"ISO", "SQL", "Postgres", "German",
+            {"ISO", "SQL", "Postgres", "German", "Sybase",                "YMD", "DMY", "MDY",                "US",
"European","NonEuropean",            "DEFAULT", NULL};