Relational Data - Functions - Date Time Functions

From FojiSoft Docs
Revision as of 18:52, 28 August 2024 by Chris.Hansen (talk | contribs) (Import ClickHouse Docs: Wed Aug 28 2024 14:52:19 GMT-0400 (Eastern Daylight Time))
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)


G | era | text | AD |
C | century of era (>=0) | number | 20 |
Y | year of era (>=0) | year | 1996 |
x | weekyear (not supported yet) | year | 1996 |
w | week of weekyear (not supported yet) | number | 27 |
e | day of week | number | 2 |
E | day of week | text | Tuesday; Tue |
y | year | year | 1996 |
D | day of year | number | 189 |
M | month of year | month | July; Jul; 07 |
d | day of month | number | 10 |
a | halfday of day | text | PM |
K | hour of halfday (0~11) | number | 0 |
h | clockhour of halfday (1~12) | number | 12 |
H | hour of day (0~23) | number | 0 |
k | clockhour of day (1~24) | number | 24 |
m | minute of hour | number | 30 |
s | second of minute | number | 55 |
S | fraction of second (not supported yet) | number | 978 |
z | time zone (short name not supported yet) | text | Pacific Standard Time; PST |
Z | time zone offset/id (not supported yet) | zone | -0800; -08:00; America/Los_Angeles |
’ | escape for text | delimiter | |
’’ | single quote | literal | ’ |

Example 

SELECT formatDateTimeInJodaSyntax(toDateTime('2010-01-04 12:34:56'), 'yyyy-MM-dd HH:mm:ss')

Result: 

┌─formatDateTimeInJodaSyntax(toDateTime('2010-01-04 12:34:56'), 'yyyy-MM-dd HH:mm:ss')─┐
│ 2010-01-04 12:34:56                                                                     │
└─────────────────────────────────────────────────────────────────────────────────────────┘

dateName

Returns specified part of date.

Syntax 

dateName(date_part, date)

Arguments

  • date_part — Date part. Possible values: ‘year’, ‘quarter’, ‘month’, ‘week’, ‘dayofyear’, ‘day’, ‘weekday’, ‘hour’, ‘minute’, ‘second’. String.
  • date — Date. Date, Date32, DateTime or DateTime64.
  • timezone — Timezone. Optional. String.

Returned value

  • The specified part of date. String

Example 

WITH toDateTime('2021-04-14 11:22:33') AS date_value
SELECT
    dateName('year', date_value),
    dateName('month', date_value),
    dateName('day', date_value);

Result: 

┌─dateName('year', date_value)─┬─dateName('month', date_value)─┬─dateName('day', date_value)─┐
│ 2021                         │ April                         │ 14                          │
└──────────────────────────────┴───────────────────────────────┴─────────────────────────────┘

monthName

Returns name of the month.

Syntax 

monthName(date)

Arguments

Returned value

Example 

WITH toDateTime('2021-04-14 11:22:33') AS date_value
SELECT monthName(date_value);

Result: 

┌─monthName(date_value)─┐
│ April                 │
└───────────────────────┘

fromUnixTimestamp

This function converts a Unix timestamp to a calendar date and a time of a day.

It can be called in two ways:

When given a single argument of type Integer, it returns a value of type DateTime, i.e. behaves like toDateTime.

Alias: FROM_UNIXTIME.

Example: 

SELECT fromUnixTimestamp(423543535);

Result: 

┌─fromUnixTimestamp(423543535)─┐
│          1983-06-04 10:58:55 │
└──────────────────────────────┘

When given two or three arguments where the first argument is a value of type Integer, Date, Date32, DateTime or DateTime64, the second argument is a constant format string and the third argument is an optional constant time zone string, the function returns a value of type String, i.e. it behaves like formatDateTime. In this case, MySQL’s datetime format style is used.

Example: 

SELECT fromUnixTimestamp(1234334543, '%Y-%m-%d %R:%S') AS DateTime;

Result: 

┌─DateTime────────────┐
│ 2009-02-11 14:42:23 │
└─────────────────────┘

See Also

fromUnixTimestampInJodaSyntax

Same as fromUnixTimestamp but when called in the second way (two or three arguments), the formatting is performed using Joda style instead of MySQL style.

Example: 

SELECT fromUnixTimestampInJodaSyntax(1234334543, 'yyyy-MM-dd HH:mm:ss', 'UTC') AS DateTime;

Result: 

┌─DateTime────────────┐
│ 2009-02-11 06:42:23 │
└─────────────────────┘

toModifiedJulianDay

Converts a Proleptic Gregorian calendar date in text form YYYY-MM-DD to a Modified Julian Day number in Int32. This function supports date from 0000-01-01 to 9999-12-31. It raises an exception if the argument cannot be parsed as a date, or the date is invalid.

Syntax 

toModifiedJulianDay(date)

Arguments

Returned value

  • Modified Julian Day number. Int32.

Example 

SELECT toModifiedJulianDay('2020-01-01');

Result: 

┌─toModifiedJulianDay('2020-01-01')─┐
│                             58849 │
└───────────────────────────────────┘

toModifiedJulianDayOrNull

Similar to toModifiedJulianDay(), but instead of raising exceptions it returns NULL.

Syntax 

toModifiedJulianDayOrNull(date)

Arguments

Returned value

Example 

SELECT toModifiedJulianDayOrNull('2020-01-01');

Result: 

┌─toModifiedJulianDayOrNull('2020-01-01')─┐
│                                   58849 │
└─────────────────────────────────────────┘

fromModifiedJulianDay

Converts a Modified Julian Day number to a Proleptic Gregorian calendar date in text form YYYY-MM-DD. This function supports day number from -678941 to 2973483 (which represent 0000-01-01 and 9999-12-31 respectively). It raises an exception if the day number is outside of the supported range.

Syntax 

fromModifiedJulianDay(day)

Arguments

Returned value

Example 

SELECT fromModifiedJulianDay(58849);

Result: 

┌─fromModifiedJulianDay(58849)─┐
│ 2020-01-01                   │
└──────────────────────────────┘

fromModifiedJulianDayOrNull

Similar to fromModifiedJulianDayOrNull(), but instead of raising exceptions it returns NULL.

Syntax 

fromModifiedJulianDayOrNull(day)

Arguments

Returned value

Example 

SELECT fromModifiedJulianDayOrNull(58849);

Result: 

┌─fromModifiedJulianDayOrNull(58849)─┐
│ 2020-01-01                         │
└────────────────────────────────────┘

toUTCTimestamp

Convert DateTime/DateTime64 type value from other time zone to UTC timezone timestamp

Syntax 

toUTCTimestamp(time_val, time_zone)

Arguments

  • time_val — A DateTime/DateTime64 type const value or an expression . DateTime/DateTime64 types
  • time_zone — A String type const value or an expression represent the time zone. String types

Returned value

  • DateTime/DateTime64 in text form

Example 

SELECT toUTCTimestamp(toDateTime('2023-03-16'), 'Asia/Shanghai');

Result: 

┌─toUTCTimestamp(toDateTime('2023-03-16'),'Asia/Shanghai')┐
│                                     2023-03-15 16:00:00 │
└─────────────────────────────────────────────────────────┘

fromUTCTimestamp

Convert DateTime/DateTime64 type value from UTC timezone to other time zone timestamp

Syntax 

fromUTCTimestamp(time_val, time_zone)

Arguments

  • time_val — A DateTime/DateTime64 type const value or an expression . DateTime/DateTime64 types
  • time_zone — A String type const value or an expression represent the time zone. String types

Returned value

  • DateTime/DateTime64 in text form

Example 

SELECT fromUTCTimestamp(toDateTime64('2023-03-16 10:00:00', 3), 'Asia/Shanghai');

Result: 

┌─fromUTCTimestamp(toDateTime64('2023-03-16 10:00:00',3),'Asia/Shanghai')─┐
│                                                 2023-03-16 18:00:00.000 │
└─────────────────────────────────────────────────────────────────────────┘

UTCTimestamp

Returns the current date and time at the moment of query analysis. The function is a constant expression.

This function gives the same result that now('UTC') would. It was added only for MySQL support and now is the preferred usage.


Syntax 

UTCTimestamp()

Alias: UTC_timestamp.

Returned value

  • Returns the current date and time at the moment of query analysis. DateTime.

Example

Query: 

SELECT UTCTimestamp();

Result: 

┌──────UTCTimestamp()─┐
│ 2024-05-28 08:32:09 │
└─────────────────────┘

timeDiff

Returns the difference between two dates or dates with time values. The difference is calculated in units of seconds. It is same as dateDiff and was added only for MySQL support. dateDiff is preferred.

Syntax 

timeDiff(first_datetime, second_datetime)
  • Arguments**

Returned value

The difference between two dates or dates with time values in seconds.

Example

Query: 

timeDiff(toDateTime64('1927-01-01 00:00:00', 3), toDate32('1927-01-02'));

Result: 

┌─timeDiff(toDateTime64('1927-01-01 00:00:00', 3), toDate32('1927-01-02'))─┐
│                                                                    86400 │
└──────────────────────────────────────────────────────────────────────────┘

Related content

Retrieved from "https://docs.foji.io/index.php?title=Relational_Data_-_Functions_-_Date_Time_Functions&oldid=2235"