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};
