You might often need to do this if you’re to aggregate daily data stretching over a longer time period and yet retain the timing
The standard way forward is to use theĀ date_part() function
Below you can find its syntax within some of the most widely used database environments
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
------------------------------------Vertica---------------------------------------------------------------- | |
select | |
current_date() as Today, | |
current_time as TimeNow, | |
current_timestamp as TodayDateAndTime, ----find current timestamp | |
Now(), | |
date_part('century', Now()) as Century, ----find current century | |
date_part('decade', Now()) as Decade, ----find current decade | |
date_part('doy', Now()) as DayOfYear, ----find day of current year | |
date_part('doq', Now()) as DayofQuarter, ----find day of current quarter | |
date_part('dow', Now()) as DayOfWeek, ----find day of current week (0 to 6) | |
date_part('isoyear', Now()) as ISOYear, ----find current year | |
date_part('isoweek', Now()) as ISOWeek, ----find current week number | |
date_part('isodow', Now()) as DayOfISOWeek, ----find day of current week (1 to 7) | |
date_part('year', Now()) as CurrentYear, ----find current year | |
date_part('quarter', Now()) as CurrentQuarter, ----find current quarter | |
date_part('month', Now()) as CurrentMonth, ----find current month | |
date_part('day', Now()) as CurrentDay, ----find current day | |
date_part('hour', Now()) as CurrentHour ----find current hour | |
------------------------------------MS SQL Server-------------------------------------------------------------------------- | |
select | |
current_timestamp as TodayDateAndTime, | |
datepart(yyyy, current_timestamp) as CurrentYear, ----find current year | |
datepart(qq, current_timestamp) as CurrentQuarter, ----find current quarter | |
datepart(mm, current_timestamp) as CurrentMonth, ----find current month | |
datepart(wk, current_timestamp) as Week, ----find current week number (count as of 1st January), | |
datepart(isowk, current_timestamp) as ISOWeek, ----find current week number (count as of 1st full week) | |
datepart(dd, current_timestamp) as CurrentDay, ----find current month | |
datepart(dy, current_timestamp) as DayOfYear, ----find day of current year | |
datepart(dw, current_timestamp) as DayOfWeek, ----find day of current week (Sun-Sat) | |
datepart(hh, current_timestamp) as Hour ----find the current hour | |
TodayDateAndTime |CurrentYear |CurrentQuarter |CurrentMonth |Week |ISOWeek |CurrentDay |DayOfYear |DayOfWeek |Hour | | |
--------------------|------------|---------------|-------------|-----|--------|-----------|----------|----------|-----| | |
2017-10-13 13:44:07 |2017 |4 |10 |43 |43 |13 |296 |2 |13 | | |
/*----------------------------------------------MySQL-----------------------------------------------------------------------*/ | |
select | |
current_timestamp as MyLocalDateAndTime, | |
extract(year from current_timestamp) as CurrentYear, #----find current year | |
extract(quarter from current_timestamp) as CurrentQuarter, #----find current quarter | |
extract(month from current_timestamp) as CurrentMonth, #----find current month | |
extract(week from current_timestamp) as CurrentWeek, #----find current week | |
extract(day from current_timestamp) as CurrentDay, #----find current day | |
extract(hour from current_timestamp) as CurrentHour #----find current hour | |
MyLocalDateAndTime |CurrentYear |CurrentQuarter |CurrentMonth |CurrentWeek |CurrentDay |CurrentHour | | |
--------------------|------------|---------------|-------------|------------|-----------|------------| | |
2017-10-13 13:44:51 |2017 |4 |10 |43 |13 |13 | | |
/*---------------------------------------- PostgreSQL: date_part----------------------------------------------------------------*/ | |
select | |
current_timestamp, | |
date_part('century', current_timestamp) as Century, ----find current century | |
date_part('decade', current_timestamp) as Decade, ----find current decade | |
date_part('doy', current_timestamp) as DayOfYear, ----find day of current year | |
date_part('dow', current_timestamp) as DayOfWeek, ----find day of current week (0 to 6) | |
date_part('isoyear', current_timestamp) as ISOYear, ----find current year | |
date_part('year', current_timestamp) as CurrentYear, ----find current year | |
date_part('quarter', current_timestamp) as CurrentQuarter, ----find current quarter | |
date_part('month', current_timestamp) as CurrentMonth, ----find current month | |
date_part('day', current_timestamp) as CurrentDay, ----find current day | |
date_part('hour', current_timestamp) as CurrentHour ----find current hour | |
now |century |decade |dayofyear |dayofweek |isoyear |currentyear |currentquarter |currentmonth |currentday |currenthour | | |
--------------------|--------|-------|----------|----------|--------|------------|---------------|-------------|-----------|------------| | |
2017-10-13 13:45:32 |21 |201 |296 |1 |2017 |2017 |4 |10 |13 |13 | | |
/*---------------------------------------- PostgreSQL: extract----------------------------------------------------------------*/ | |
select | |
current_timestamp, | |
extract(century from current_timestamp) as Century, ----find current century | |
extract(decade from current_timestamp) as Decade, ----find current decade | |
extract(doy from current_timestamp) as DayOfYear, ----find day of current year | |
extract(dow from current_timestamp) as DayOfWeek, ----find day of current week (0 to 6) | |
extract(isoyear from current_timestamp) as ISOYear, ----find current year | |
extract(year from current_timestamp) as CurrentYear, ----find current year | |
extract(quarter from current_timestamp) as CurrentQuarter, ----find current quarter | |
extract(month from current_timestamp) as CurrentMonth, ----find current month | |
extract(day from current_timestamp) as CurrentDay, ----find current day | |
extract(hour from current_timestamp) as CurrentHour ----find current hour | |
now |century |decade |dayofyear |dayofweek |isoyear |currentyear |currentquarter |currentmonth |currentday |currenthour | | |
--------------------|--------|-------|----------|----------|--------|------------|---------------|-------------|-----------|------------| | |
2017-10-13 13:45:32 |21 |201 |296 |1 |2017 |2017 |4 |10 |13 |13 | | |
Curious for alternative approaches?