using Derby database Timestamps intelligently

[FerencMantfeld]

In Derby, you cannot use functions like to_char or Convert or CAST when dealing with datetime and timestamp data types.

Instead, we have extended our Derby repository with the following functions:
format_timestamp and format_date.

The syntax is as follows:
select format_timestamp( column_name, 'format') as Column_alias:

An example:
select format_timestamp (START_TIME,'dd-MMM-yyyy') as RUN_DATE
, format_timestamp (START_TIME,'yyyyMMdd') as SORTKEY
from ...... where <CONDITION>

Supported date formats-:
Letter Date or Time Component Presentation Examples
G Era designator Text AD
y Year Year 1996; 96
M Month in year Month July; Jul; 07
w Week in year Number 27
W Week in month Number 2
D Day in year Number 189
d Day in month Number 10
F Day of week in month Number 2
E Day in week Text Tuesday; Tue
a Am/pm marker Text PM
H Hour in day (0-23) Number 0
k Hour in day (1-24) Number 24
K Hour in am/pm (0-11) Number 0
h Hour in am/pm (1-12) Number 12
m Minute in hour Number 30
s Second in minute Number 55
S Millisecond Number 978
z Time zone General time zone Pacific Standard Time; PST; GMT-08:00
Z Time zone RFC 822 time zone -0800

[kiran]

Hello,
I tried using the function suggested by you in derby query, but i got an error saying format_timestamp not recognized.
can you pls provide some more information which i am missing.

[FerencMantfeld]

As the thread states, our repository runs on Derby and we have extended the repository with our own built-in functions so this is only available as is to implementations where the Derby database contains our repository

[archana.kulkarni]

Hello Kiran,

We run the following statement on our Derby Database

create function format_timestamp
( timeStamp timestamp, date_format varchar(50) )
returns varchar(50)
parameter style java
no sql
language java
external name 'Function.formatTimestamp';

And Function.java is a java class which has a method called formatTimestamp which has the logic to convert the timestamp to the required format.