Mysql Date Time Helpers

Time & Date Functions Documentation

This guide covers time and date conversion/formatting functions in the framework, including timezone support and localization.

1. convertTo24HourFormat()

Converts 12-hour time format (AM/PM) to 24-hour format.

Syntax

convertTo24HourFormat(time);

Parameters

Parameter	Type	Description
`time`	string	Time string in 12-hour format (e.g., “02:30 PM”, “12:00 AM”)

Returns

string: Time in 24-hour format (e.g., “14:30”, “00:00”)

Examples

// Convert PM time
convertTo24HourFormat("02:30 PM"); // Returns: "14:30"
convertTo24HourFormat("11:45 PM"); // Returns: "23:45"

// Convert AM time
convertTo24HourFormat("09:15 AM"); // Returns: "09:15"
convertTo24HourFormat("12:00 AM"); // Returns: "00:00"

// 12 PM (noon)
convertTo24HourFormat("12:00 PM"); // Returns: "12:00"

Use Cases

Converting user input from 12-hour to 24-hour format
Preparing time data for database storage
Standardizing time formats

2. formatTimeForMySQL()

Converts time to MySQL DATETIME format with timezone support. Converts local time to UTC for database storage. Can also return only the time portion for MySQL TIME columns.

Syntax

formatTimeForMySQL(time, (timezone = "+5:30"), (includeMilliseconds = false), (timeOnly = false));

Parameters

Parameter	Type	Default	Description
`time`	Date\|number\|string	required	Time input (Date object, timestamp, or time string)
`timezone`	string\|number	`"+5:30"`	Timezone offset (e.g., “+5:30”, “IST”, or offset in minutes)
`includeMilliseconds`	boolean	`false`	Whether to include milliseconds in output. `true` = include, `false` = exclude
`timeOnly`	boolean	`false`	Whether to return only time portion. `true` = “HH:mm:ss”, `false` = full “YYYY-MM-DD HH:mm:ss”

Returns

string: MySQL DATETIME format string (e.g., “2024-01-15 14:30:45” or “2024-01-15 14:30:45.123”)
string: MySQL TIME format string (e.g., “14:30:45” or “14:30:45.123”) when timeOnly = true
null: If input is invalid

Boolean Parameters Explained

`includeMilliseconds` Parameter

false (default): Returns format without milliseconds
- Example: "2024-01-15 14:30:45"
true: Returns format with milliseconds
- Example: "2024-01-15 14:30:45.123"

Purpose: Use true when you need microsecond precision for timestamps. Most use cases don’t need milliseconds, so false is the default.

`timeOnly` Parameter

false (default): Returns full DATETIME format
- Example: "2024-01-15 14:30:45"
- Use case: Storing complete date and time in DATETIME or TIMESTAMP columns
true: Returns only time portion
- Example: "14:30:45"
- Use case: Storing only time in MySQL TIME columns, or when you only need the time component

Purpose: Use true when storing time-only values (like “14:35:00”) in MySQL TIME columns, or when you only need the time portion without the date.

Timezone Formats Supported

String formats: "+5:30", "+05:30", "+0530", "-5:00", "IST", "UTC", "GMT"
Number format: Offset in minutes (e.g., 330 for IST)

Examples

Basic Usage

// Current time to MySQL format (IST timezone)
formatTimeForMySQL(new Date());
// Returns: "2024-01-15 08:30:45" (converted to UTC)

// With timestamp
formatTimeForMySQL(1705312245000);
// Returns: "2024-01-15 08:30:45"

// With time string (uses today's date)
formatTimeForMySQL("14:30:00");
// Returns: "2024-01-15 09:00:00" (14:30 IST = 09:00 UTC)

With Different Timezones

// IST (default)
formatTimeForMySQL(new Date(), "+5:30");
formatTimeForMySQL(new Date(), "IST");

// UTC
formatTimeForMySQL(new Date(), "UTC");
formatTimeForMySQL(new Date(), "+0:00");

// US Eastern Time (UTC-5)
formatTimeForMySQL(new Date(), "-5:00");

// Using offset in minutes
formatTimeForMySQL(new Date(), 330); // IST
formatTimeForMySQL(new Date(), -300); // UTC-5

With Milliseconds

formatTimeForMySQL(new Date(), "+5:30", true);
// Returns: "2024-01-15 08:30:45.123"

// Time only with milliseconds
formatTimeForMySQL(new Date(), "+5:30", true, true);
// Returns: "14:30:45.123"

Time Only Format

// Return only time portion (for MySQL TIME columns)
formatTimeForMySQL(new Date(), "+5:30", false, true);
// Returns: "14:30:45"

// Time only with milliseconds
formatTimeForMySQL(new Date(), "+5:30", true, true);
// Returns: "14:30:45.123"

// With time string input
formatTimeForMySQL("14:35:00", "+5:30", false, true);
// Returns: "09:05:00" (converted to UTC)

// Common use case: Store time in TIME column
var timeValue = formatTimeForMySQL(new Date(), "+5:30", false, true);
// Returns: "14:30:45" - ready for MySQL TIME column

Use Cases

Storing timestamps in database: Convert local time to UTC for consistent storage
- Use timeOnly = false for DATETIME/TIMESTAMP columns
- Use timeOnly = true for TIME columns
API requests: Send properly formatted datetime to backend
Data logging: Standardize time format for logs
Time-only fields: Store time values without date (e.g., “14:35:00”)

Important Notes

Converts to UTC: This function converts the input time to UTC for database storage
Timezone handling: The timezone parameter specifies what timezone the input time is in
Default timezone: Defaults to IST (+5:30) if not specified
Time-only mode: When timeOnly = true, returns only “HH:mm:ss” format suitable for MySQL TIME columns
Parameter order: Parameters are positional, so you can skip optional ones but must provide them in order:
- formatTimeForMySQL(time) - Uses all defaults
- formatTimeForMySQL(time, timezone) - Custom timezone, default milliseconds and timeOnly
- formatTimeForMySQL(time, timezone, includeMilliseconds) - Custom timezone and milliseconds
- formatTimeForMySQL(time, timezone, includeMilliseconds, timeOnly) - All parameters

3. formatMySQLTimeFromUTC()

Converts MySQL UTC DATETIME to a specific timezone for display. Converts UTC from database to local timezone.

Syntax

formatMySQLTimeFromUTC(mysqlDateTime, (timezone = "+5:30"), (outputFormat = null));

Parameters

Parameter	Type	Default	Description
`mysqlDateTime`	string\|Date\|number	required	MySQL DATETIME string (UTC) or Date object or timestamp
`timezone`	string\|number	`"+5:30"`	Target timezone offset (e.g., “+5:30”, “IST”)
`outputFormat`	string	`null`	Format string (e.g., “YYYY-MM-DD HH:mm:ss”, “DD.MM.YYYY”)

Returns

Date object: If outputFormat is null
string: Formatted string in the target timezone if format is provided
null: If input is invalid

Format Placeholders

Placeholder	Description	Example
`YYYY`	4-digit year	2024
`YY`	2-digit year	24
`MM`	2-digit month	01, 12
`DD`	2-digit day	01, 31
`HH`	24-hour format	00-23
`hh`	12-hour format	01-12
`mm`	Minutes	00-59
`ss`	Seconds	00-59
`sss`	Milliseconds	000-999
`AMPM`	AM/PM	AM, PM

Examples

Basic Usage

// MySQL UTC datetime string
formatMySQLTimeFromUTC("2024-01-15 08:30:45", "+5:30");
// Returns: Date object for 2024-01-15 14:00:45 IST

// With format
formatMySQLTimeFromUTC("2024-01-15 08:30:45", "+5:30", "DD.MM.YYYY");
// Returns: "15.01.2024"

formatMySQLTimeFromUTC("2024-01-15 08:30:45", "+5:30", "DD.MM.YYYY HH:mm");
// Returns: "15.01.2024 14:00"

Common Format Examples

// Date only
formatMySQLTimeFromUTC("2024-01-15 08:30:45", "+5:30", "DD.MM.YYYY");
// Returns: "15.01.2024"

// Date and time
formatMySQLTimeFromUTC("2024-01-15 08:30:45", "+5:30", "DD.MM.YYYY HH:mm:ss");
// Returns: "15.01.2024 14:00:45"

// 12-hour format
formatMySQLTimeFromUTC("2024-01-15 08:30:45", "+5:30", "DD.MM.YYYY hh:mm AMPM");
// Returns: "15.01.2024 02:00 PM"

// Time only
formatMySQLTimeFromUTC("2024-01-15 08:30:45", "+5:30", "HH:mm");
// Returns: "14:00"

// Short date
formatMySQLTimeFromUTC("2024-01-15 08:30:45", "+5:30", "DD.MM.YY");
// Returns: "15.01.24"

With Different Timezones

// IST
formatMySQLTimeFromUTC("2024-01-15 08:30:45", "+5:30", "DD.MM.YYYY HH:mm");
formatMySQLTimeFromUTC("2024-01-15 08:30:45", "IST", "DD.MM.YYYY HH:mm");

// UTC
formatMySQLTimeFromUTC("2024-01-15 08:30:45", "UTC", "DD.MM.YYYY HH:mm");
// Returns: "15.01.2024 08:30"

// US Eastern Time
formatMySQLTimeFromUTC("2024-01-15 08:30:45", "-5:00", "DD.MM.YYYY HH:mm");
// Returns: "15.01.2024 03:30"

With Date Object or Timestamp

// Date object
formatMySQLTimeFromUTC(new Date(), "+5:30", "DD.MM.YYYY");

// Timestamp
formatMySQLTimeFromUTC(1705312245000, "+5:30", "DD.MM.YYYY HH:mm");

Use Cases

Displaying database timestamps: Convert UTC from database to user’s local timezone
Date formatting: Format dates for display in various formats
Localization: Show dates in user’s preferred timezone

Important Notes

Converts from UTC: This function converts UTC time from database to target timezone
Default timezone: Defaults to IST (+5:30) if not specified
Returns Date object: If no format is provided, returns a Date object

4. formatDate()

Formats dates with language support and number conversion. Supports multiple input formats and output formats with localization.

Syntax

formatDate(inputDate, outputFormat);

Parameters

Parameter	Type	Description
`inputDate`	Date\|number\|string	Date input (Date object, timestamp, or various string formats)
`outputFormat`	string	Format string (e.g., “DD.MM.YYYY”, “DD MMMM YYYY”)

Returns

string: Formatted date string
null: If input is invalid

Supported Input Formats

Date object: new Date()
Timestamp: 1705312245000
ISO format: "2024-01-15T14:30:45.123Z"
MySQL format: "2024-01-15" or "2024-01-15 14:30:45"
DD/MM/YYYY: "15/01/2024"
DD.MM.YYYY: "15.01.2024"
DD/MM/YY: "15/01/24" (assumes 20YY if < 50, else 19YY)
JavaScript toString: "Mon Feb 12 2024 18:45:30 GMT+0530"

Format Placeholders

Placeholder	Description	Example
`YYYY`	4-digit year	2024
`YY`	2-digit year	24
`MM`	2-digit month	01, 12
`DD`	2-digit day	01, 31
`Do`	Day with ordinal	1st, 2nd, 3rd, 15th
`OOO`	Short month name	Jan, Feb, Mar (localized)
`NNNN`	Full month name	January, February (localized)
`HH`	24-hour format	00-23
`hh`	12-hour format	01-12
`mm`	Minutes	00-59
`ss`	Seconds	00-59
`AMPM`	AM/PM	AM, PM

Language Support

The function automatically uses the current language from global("current_language"):

English: Standard English month names and numbers
Hindi: Hindi month names and Devanagari numerals (०-९)
Kannada: Kannada month names and Kannada numerals (೦-೯)

Number conversion can be enabled/disabled via global("convert_number").

Examples

Basic Usage

// Current date
formatDate(new Date(), "DD.MM.YYYY");
// Returns: "15.01.2024"

// With timestamp
formatDate(1705312245000, "DD.MM.YYYY");
// Returns: "15.01.2024"

// With string input
formatDate("2024-01-15", "DD.MM.YYYY");
// Returns: "15.01.2024"

formatDate("15/01/2024", "DD.MM.YYYY");
// Returns: "15.01.2024"

Common Format Examples

// Date only
formatDate(new Date(), "DD.MM.YYYY"); // "15.01.2024"
formatDate(new Date(), "DD/MM/YYYY"); // "15/01/2024"
formatDate(new Date(), "DD-MM-YYYY"); // "15-01-2024"
formatDate(new Date(), "DD.MM.YY"); // "15.01.24"

// With month names
formatDate(new Date(), "DD OOO YYYY"); // "15 Jan 2024"
formatDate(new Date(), "DD NNNN YYYY"); // "15 January 2024"
formatDate(new Date(), "DD Do NNNN YYYY"); // "15th January 2024"

// With time
formatDate(new Date(), "DD.MM.YYYY HH:mm"); // "15.01.2024 14:30"
formatDate(new Date(), "DD.MM.YYYY hh:mm AMPM"); // "15.01.2024 02:30 PM"
formatDate(new Date(), "DD.MM.YYYY HH:mm:ss"); // "15.01.2024 14:30:45"

With Localization

// If current language is Hindi and convert_number is true
formatDate(new Date(), "DD.MM.YYYY");
// Returns: "१५.०१.२०२४" (with Devanagari numerals)

formatDate(new Date(), "DD NNNN YYYY");
// Returns: "१५ जनवरी २०२४" (Hindi month name)

// If current language is Kannada
formatDate(new Date(), "DD NNNN YYYY");
// Returns: "೧೫ ಜನವರಿ ೨೦೨೪" (Kannada month name and numerals)

With Different Input Formats

// MySQL format
formatDate("2024-01-15", "DD.MM.YYYY");
// Returns: "15.01.2024"

// ISO format
formatDate("2024-01-15T14:30:45Z", "DD.MM.YYYY HH:mm");
// Returns: "15.01.2024 14:30"

// DD/MM/YYYY format
formatDate("15/01/2024", "DD.MM.YYYY");
// Returns: "15.01.2024"

// DD/MM/YY format (2-digit year)
formatDate("15/01/24", "DD.MM.YYYY");
// Returns: "15.01.2024" (assumes 2024)

formatDate("15/01/99", "DD.MM.YYYY");
// Returns: "15.01.1999" (assumes 1999 for years >= 50)

Use Cases

Display dates: Format dates for user display
Localization: Show dates in user’s language with localized month names and numerals
Date parsing: Parse various date formats into a standard format
UI display: Format dates for forms, lists, and reports

Important Notes

Language support: Automatically uses current language from global variables
Number conversion: Respects global("convert_number") setting
Flexible input: Accepts many common date formats
Ordinal suffixes: Supports “1st”, “2nd”, “3rd”, etc. with Do placeholder

5. dateDiff()

Calculates the difference between a given date and today in specified units.

Syntax

dateDiff(givenDate, (unit = "days"));

Parameters

Parameter	Type	Default	Description
`givenDate`	string	required	Date string in DD.MM.YYYY format
`unit`	string	`"days"`	Unit of difference: “years”, “months”, “days”, “hours”, “minutes”, “seconds”

Returns

number: Difference in the specified unit (rounded down)

Supported Units

"years" - Difference in years
"months" - Difference in months (approximate, 30 days per month)
"days" - Difference in days
"hours" - Difference in hours
"minutes" - Difference in minutes
"seconds" - Difference in seconds

Examples

// Today is 2024-01-20

// Days difference
dateDiff("15.01.2024", "days"); // Returns: 5 (5 days ago)
dateDiff("25.01.2024", "days"); // Returns: -5 (5 days in future)

// Months difference
dateDiff("20.12.2023", "months"); // Returns: 1 (1 month ago)
dateDiff("20.02.2024", "months"); // Returns: -1 (1 month in future)

// Years difference
dateDiff("20.01.2023", "years"); // Returns: 1 (1 year ago)
dateDiff("20.01.2025", "years"); // Returns: -1 (1 year in future)

// Hours difference
dateDiff("20.01.2024", "hours"); // Returns: ~0 (same day)
dateDiff("19.01.2024", "hours"); // Returns: ~24 (1 day = 24 hours)

// Minutes difference
dateDiff("20.01.2024", "minutes"); // Returns: ~0 (same day)

Use Cases

Age calculation: Calculate age from birth date
Deadline tracking: Check how many days until/since a deadline
Date comparisons: Compare dates to today
Expiry checks: Check if something has expired

Important Notes

Input format: Only accepts DD.MM.YYYY format
Negative values: Returns negative values for future dates
Rounded down: Uses Math.floor() to round down the result
Month approximation: Months are calculated as 30 days (approximate)

Complete Examples

Example 1: Storing and Displaying Timestamps

// User selects time: 2:30 PM IST
var userTime = "02:30 PM";

// Convert to 24-hour format
var time24 = convertTo24HourFormat(userTime); // "14:30"

// Create date with this time
var date = new Date();
date.setHours(14, 30, 0);

// Store in database (convert to UTC) - Full DATETIME
var mysqlDateTime = formatTimeForMySQL(date, "+5:30", false, false);
// Returns: "2024-01-15 09:00:00" (14:30 IST = 09:00 UTC)

// Store only time in TIME column
var mysqlTime = formatTimeForMySQL(date, "+5:30", false, true);
// Returns: "09:00:00" (time only, converted to UTC)

// Later, retrieve and display
var displayTime = formatMySQLTimeFromUTC(mysqlDateTime, "+5:30", "DD.MM.YYYY hh:mm AMPM");
// Returns: "15.01.2024 02:30 PM"

Example 1b: Storing Time-Only Values

// User enters time: 14:35:00
var userTime = "14:35:00";

// Store as TIME value (timeOnly = true)
var mysqlTime = formatTimeForMySQL(userTime, "+5:30", false, true);
// Returns: "09:05:00" (14:35 IST = 09:05 UTC)

// Use in SQL INSERT for TIME column
// INSERT INTO schedule (start_time) VALUES ('09:05:00');

Example 2: Date Formatting with Localization

// Get date from API (MySQL format)
var apiDate = "2024-01-15 14:30:45";

// Format for display (IST timezone)
var formatted = formatMySQLTimeFromUTC(apiDate, "+5:30", "DD.MM.YYYY");
// Returns: "15.01.2024"

// Format with month name
var withMonth = formatDate(formatted, "DD NNNN YYYY");
// Returns: "15 January 2024" (or localized if language is set)

// If language is Hindi and convert_number is true
// Returns: "१५ जनवरी २०२४"

Example 3: Date Difference Calculation

// Check if order is overdue
var returnDate = "25.01.2024";
var daysLeft = dateDiff(returnDate, "days");

if (daysLeft < 0) {
  console.log("Order is overdue by " + Math.abs(daysLeft) + " days");
} else if (daysLeft === 0) {
  console.log("Order is due today");
} else {
  console.log("Order is due in " + daysLeft + " days");
}

Example 4: Complete Date Workflow

// 1. User enters date in form: "15.01.2024"
var userDate = "15.01.2024";

// 2. Format for display
var displayDate = formatDate(userDate, "DD NNNN YYYY");
// Returns: "15 January 2024"

// 3. Convert to MySQL format for storage
var dateObj = new Date(userDate.split(".").reverse().join("-"));
var mysqlDate = formatTimeForMySQL(dateObj, "+5:30");
// Returns: "2024-01-15 00:00:00"

// 4. Store in database...

// 5. Retrieve and display
var retrievedDate = formatMySQLTimeFromUTC(mysqlDate, "+5:30", "DD.MM.YYYY");
// Returns: "15.01.2024"

// 6. Calculate days since
var daysSince = dateDiff(retrievedDate, "days");
// Returns: difference in days from today

Quick Reference

Function Comparison

Function	Purpose	Input	Output	Timezone
`convertTo24HourFormat()`	Convert 12h to 24h	”02:30 PM"	"14:30”	N/A
`formatTimeForMySQL()`	Local → UTC for DB	Date/Time	MySQL DATETIME/TIME	Converts to UTC
`formatMySQLTimeFromUTC()`	UTC → Local for display	MySQL DATETIME	Formatted string	Converts from UTC
`formatDate()`	Format any date	Date/Time	Formatted string	Uses local time
`dateDiff()`	Calculate difference	DD.MM.YYYY	Number	N/A

Common Patterns

// Store current time in database (full DATETIME)
var mysqlDateTime = formatTimeForMySQL(new Date(), "+5:30", false, false);
// Returns: "2024-01-15 14:30:45"

// Store only time in TIME column
var mysqlTime = formatTimeForMySQL(new Date(), "+5:30", false, true);
// Returns: "14:30:45"

// Store with milliseconds (high precision)
var mysqlDateTimeMs = formatTimeForMySQL(new Date(), "+5:30", true, false);
// Returns: "2024-01-15 14:30:45.123"

// Display database time to user
var displayTime = formatMySQLTimeFromUTC(mysqlDateTime, "+5:30", "DD.MM.YYYY hh:mm AMPM");

// Format any date
var formatted = formatDate(new Date(), "DD.MM.YYYY");

// Check days until date
var days = dateDiff("25.01.2024", "days");

Notes

Timezone Default: All functions default to IST (+5:30) if timezone is not specified
UTC Storage: Always store times in UTC in the database using formatTimeForMySQL()
Local Display: Always convert from UTC to local timezone for display using formatMySQLTimeFromUTC()
Language Support: formatDate() automatically uses current language settings
Input Flexibility: Most functions accept Date objects, timestamps, and various string formats
Error Handling: Functions return null if input is invalid
Boolean Parameters:
- includeMilliseconds: false = no milliseconds (default), true = include milliseconds
- timeOnly: false = full DATETIME format (default), true = time only “HH:mm:ss” format
MySQL Column Types:
- Use timeOnly = false for DATETIME or TIMESTAMP columns
- Use timeOnly = true for TIME columns

Mysql Date Time Helpers

Time & Date Functions Documentation

1. convertTo24HourFormat()

Syntax

Parameters

Returns

Examples

Use Cases

2. formatTimeForMySQL()

Syntax

Parameters

Returns

Boolean Parameters Explained

includeMilliseconds Parameter

timeOnly Parameter

Timezone Formats Supported

Examples

Basic Usage

With Different Timezones

With Milliseconds

Time Only Format

Use Cases

Important Notes

3. formatMySQLTimeFromUTC()

Syntax

Parameters

Returns

Format Placeholders

Examples

Basic Usage

Common Format Examples

With Different Timezones

With Date Object or Timestamp

Use Cases

Important Notes

4. formatDate()

Syntax

Parameters

Returns

Supported Input Formats

Format Placeholders

Language Support

Examples

Basic Usage

Common Format Examples

With Localization

With Different Input Formats

Use Cases

Important Notes

5. dateDiff()

Syntax

Parameters

Returns

Supported Units

Examples

Use Cases

Important Notes

Complete Examples

Example 1: Storing and Displaying Timestamps

Example 1b: Storing Time-Only Values

Example 2: Date Formatting with Localization

Example 3: Date Difference Calculation

Example 4: Complete Date Workflow

Quick Reference

Function Comparison

Common Patterns

Notes

`includeMilliseconds` Parameter

`timeOnly` Parameter