/*
* Copyright 2004-2011 H2 Group. Multiple-Licensed under the H2 License,
* Version 1.0, and under the Eclipse Public License, Version 1.0
* (http://h2database.com/html/license.html).
* Initial Developer: Jason Brittain (jason.brittain at gmail.com)
*/
package org.h2.mode;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
import org.h2.util.StringUtils;
/**
* This class implements some MySQL-specific functions.
*
* @author Jason Brittain
* @author Thomas Mueller
*/
public class FunctionsMySQL {
/**
* The date format of a MySQL formatted date/time.
* Example: 2008-09-25 08:40:59
*/
private static final String DATE_TIME_FORMAT = "yyyy-MM-dd HH:mm:ss";
/**
* Format replacements for MySQL date formats.
* See
* http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
*/
private static final String[] FORMAT_REPLACE = {
"%a", "EEE",
"%b", "MMM",
"%c", "MM",
"%d", "dd",
"%e", "d",
"%H", "HH",
"%h", "hh",
"%I", "hh",
"%i", "mm",
"%j", "DDD",
"%k", "H",
"%l", "h",
"%M", "MMMM",
"%m", "MM",
"%p", "a",
"%r", "hh:mm:ss a",
"%S", "ss",
"%s", "ss",
"%T", "HH:mm:ss",
"%W", "EEEE",
"%w", "F",
"%Y", "yyyy",
"%y", "yy",
"%%", "%",
};
/**
* Register the functionality in the database.
* Nothing happens if the functions are already registered.
*
* @param conn the connection
*/
public static void register(Connection conn) throws SQLException {
String[] init = {
"UNIX_TIMESTAMP", "unixTimestamp",
"FROM_UNIXTIME", "fromUnixTime",
"DATE", "date",
};
Statement stat = conn.createStatement();
for (int i = 0; i < init.length; i += 2) {
String alias = init[i], method = init[i + 1];
stat.execute(
"CREATE ALIAS IF NOT EXISTS " + alias +
" FOR \"" + FunctionsMySQL.class.getName() + "." + method + "\"");
}
}
/**
* Get the seconds since 1970-01-01 00:00:00 UTC.
* See
* http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestamp
*
* @return the current timestamp in seconds (not milliseconds).
*/
public static int unixTimestamp() {
return (int) (System.currentTimeMillis() / 1000L);
}
/**
* Get the seconds since 1970-01-01 00:00:00 UTC of the given timestamp.
* See
* http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestamp
*
* @param timestamp the timestamp
* @return the current timestamp in seconds (not milliseconds).
*/
public static int unixTimestamp(java.sql.Timestamp timestamp) {
return (int) (timestamp.getTime() / 1000L);
}
/**
* See
* http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime
*
* @param seconds The current timestamp in seconds.
* @return a formatted date/time String in the format "yyyy-MM-dd HH:mm:ss".
*/
public static String fromUnixTime(int seconds) {
SimpleDateFormat formatter = new SimpleDateFormat(DATE_TIME_FORMAT, Locale.ENGLISH);
return formatter.format(new Date(seconds * 1000L));
}
/**
* See
* http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime
*
* @param seconds The current timestamp in seconds.
* @param format The format of the date/time String to return.
* @return a formatted date/time String in the given format.
*/
public static String fromUnixTime(int seconds, String format) {
format = convertToSimpleDateFormat(format);
SimpleDateFormat formatter = new SimpleDateFormat(format, Locale.ENGLISH);
return formatter.format(new Date(seconds * 1000L));
}
private static String convertToSimpleDateFormat(String format) {
String[] replace = FORMAT_REPLACE;
for (int i = 0; i < replace.length; i += 2) {
format = StringUtils.replaceAll(format, replace[i], replace[i + 1]);
}
return format;
}
/**
* See
* http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date
* This function is dependent on the exact formatting of the MySQL date/time
* string.
*
* @param dateTime The date/time String from which to extract just the date
* part.
* @return the date part of the given date/time String argument.
*/
public static String date(String dateTime) {
if (dateTime == null) {
return null;
}
int index = dateTime.indexOf(' ');
if (index != -1) {
return dateTime.substring(0, index);
}
return dateTime;
}
}