currentTimeMillis reverse lookup, AKA "from currentTimeMillis to date" in WLST

If you have a timestamp expressed as

System.currentTimeMillis()

=1373941644513  

you can transform it into a readable date this way:

from java.text import SimpleDateFormat 

from java.util import Date

sdf = SimpleDateFormat("MMM dd,yyyy HH:mm")

mydate = Date(1373941644513)

print sdf.format(mydate)

Jul 16,2013 04:27

Oracle DB dates

Official Doc on DATE datatype:

http://docs.oracle.com/cd/B19306_01/server.102/b14220/datatype.htm#i1847

Use TO_CHAR(mydate, 'MM/DD/YYYY HH24:MI:SS') to display the Date

WARNING: when you export your tables in Oracle SQL Developer using the "INSERT" format, DATE columns are TRUNCATED to midnigth, with a to_date('22-JUN-12','DD-MON-RR') statement.


Use
TO_DATE('2012-08-13 11.20.00', 'YYYY-MM-DD HH24:MI:SS')

to do the opposite conversion

Timezones and Daylight savings

In XQuery, the dateTime is associated with a TimeZone - expressed as a difference from GMT, the +01:00 below:

fn:current-dateTime()
2012-02-07T11:33:37.829+01:00


to convert this dateTime to GMT, use this trick:

fn:adjust-dateTime-to-timezone(fn:current-dateTime(),xdt:dayTimeDuration('PT0H'))
2012-02-07T10:33:37.829Z

to discover which is your timezone:

fn:implicit-timezone()
PT1H

To convert a String to dateTime:
xs:dateTime("2012-02-07T10:39:27")

To convert a String to date:
xs:date("2012-02-07")

So instead of fn:current-dateTime() you can pass an explicit dateTime:

fn:adjust-dateTime-to-timezone(xs:dateTime("2012-02-07T11:33:37.829+01:00"),xdt:dayTimeDuration('PT0H'))
2012-02-07T10:33:37.829Z

(since I am in +1 timeZone, the hour goes back 1 hour after adjustment)

BEWARE: the difference with GMT changes with the DayLight savings period (23 march to 22 September approximately)

setting the clock to june the 14th, the difference becomes 2 hours:

fn:current-dateTime()
2012-06-14T11:50:36.124+02:00

but our system to adjust dateTime to GMT still works perfectly:

fn:adjust-dateTime-to-timezone(fn:current-dateTime(),xdt:dayTimeDuration('PT0H'))
2012-06-14T09:50:36.124Z

Oracle DB: difference between date and timestamp

http://www.databasejournal.com/features/oracle/article.php/2234501/A-Comparison-of-Oracles-DATE-and-TIMESTAMP-Datatypes.htm

basically: use DATE in "normal" cases, and TIMESTAMP when computing time intervals is important.

in SQLDeveloper:

create table TESTDATES (
column1 date,
column2 timestamp(6)
)

first do this to display dates in all their splendor:

alter session set NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';



If you use the JPA wizard in Eclipse, you will find that DATE is mapped to a java.util.Date (with a @Temporal annotation), while TIMESTAMP is mapped to a java.sql.Timestamp

@Temporal( TemporalType.DATE)
private Date column1;

private Timestamp column2;