tipo - La mejor forma de convertir DateTime a "n Hours Ago" en SQL

sql date format dd/mm/yyyy

Question

Escribí una función SQL para convertir un valor de fecha y hora en SQL a un tipo de mensaje más amigable "n Hours Ago" o "n Days Ago", etc. Y me preguntaba si había una mejor manera de hacerlo.

(Sí, sé "no hacerlo en SQL", pero por razones de diseño tengo que hacerlo de esta manera).

Aquí está la función que he escrito:

CREATE FUNCTION dbo.GetFriendlyDateTimeValue
(
@CompareDate DateTime
)
RETURNS nvarchar(48)
AS
BEGIN
DECLARE @Now DateTime
DECLARE @Hours int
DECLARE @Suff nvarchar(256)
DECLARE @Found bit
SET @Found = 0
SET @Now = getDate()
SET @Hours = DATEDIFF(MI, @CompareDate, @Now)/60
IF @Hours <= 1
BEGIN
SET @Suff = ''Just Now''
SET @Found = 1
RETURN @Suff
END
IF @Hours < 24
BEGIN
SET @Suff = '' Hours Ago''
SET @Found = 1
END
IF @Hours >= 8760 AND @Found = 0
BEGIN
SET @Hours = @Hours / 8760
SET @Suff = '' Years Ago''
SET @Found = 1
END
IF @Hours >= 720 AND @Found = 0
BEGIN
SET @Hours = @Hours / 720
SET @Suff = '' Months Ago''
SET @Found = 1
END
IF @Hours >= 168 AND @Found = 0
BEGIN
SET @Hours = @Hours / 168
SET @Suff = '' Weeks Ago''
SET @Found = 1
END
IF @Hours >= 24 AND @Found = 0
BEGIN
SET @Hours = @Hours / 24
SET @Suff = '' Days Ago''
SET @Found = 1
END
RETURN Convert(nvarchar, @Hours) + @Suff
END

Answer 1

¿Qué tal esto? Puede expandir este patrón para hacer mensajes de "años" y puede marcar "1 día" o "1 hora" para que no diga "1 día atrás" ...

Me gusta la sentencia CASE en SQL.

drop function dbo.time_diff_message
GO
create function dbo.time_diff_message (
@input_date datetime
)
returns varchar(200)
as
begin
declare @msg varchar(200)
declare @hourdiff int
set @hourdiff = datediff(hour, @input_date, getdate())
set @msg = case when @hourdiff < 0 then '' from now'' else '' ago'' end
set @hourdiff = abs(@hourdiff)
set @msg = case when @hourdiff > 24 then convert(varchar, @hourdiff/24) + '' days'' + @msg
else convert(varchar, @hourdiff) + '' hours'' + @msg
end
return @msg
end
GO
select dbo.time_diff_message(''Dec 7 1941'')

Answer 2

Como dices, probablemente no lo haría en SQL, pero como ejercicio de pensamiento tengo una implementación de MySQL:

CASE
WHEN compare_date between date_sub(now(), INTERVAL 60 minute) and now()
THEN concat(minute(TIMEDIFF(now(), compare_date)), '' minutes ago'')
WHEN datediff(now(), compare_date) = 1
THEN ''Yesterday''
WHEN compare_date between date_sub(now(), INTERVAL 24 hour) and now()
THEN concat(hour(TIMEDIFF(NOW(), compare_date)), '' hours ago'')
ELSE concat(datediff(now(), compare_date),'' days ago'')
END

Basado en una muestra similar vista en las páginas de manual de MySQL Date and Time

Answer 3

En Oracle:

select
CC.MOD_DATETIME,
''Last modified '' ||
case when (sysdate - cc.mod_datetime) < 1
then round((sysdate - CC.MOD_DATETIME)*24) || '' hours ago''
when (sysdate - CC.MOD_DATETIME) between 1 and 7
then round(sysdate-CC.MOD_DATETIME) || '' days ago''
when (sysdate - CC.MOD_DATETIME) between 8 and 365
then round((sysdate - CC.MOD_DATETIME) / 7) || '' weeks ago''
when (sysdate - CC.MOD_DATETIME) > 365
then round((sysdate - CC.MOD_DATETIME) / 365) || '' years ago''
end
from
customer_catalog CC

Answer 4

Tu código parece funcional. En cuanto a una mejor manera, eso se volverá subjetivo. Es posible que desee verificar esta página, ya que se trata de períodos de tiempo en SQL.

Answer 5

Mi intento: esto es para MS SQL. Admite ''ago'' y ''desde ahora'', pluralización y no utiliza redondeo o fechada, pero truncamiento - fechada da diferencia de 1 mes entre 8/30 y 9/1 que probablemente no es lo que desea. Redondeo da diferencia de 1 mes entre 9/1 y 9/16. De nuevo, probablemente no sea lo que quieres.

CREATE FUNCTION dbo.GetFriendlyDateTimeValue( @CompareDate DATETIME ) RETURNS NVARCHAR(48) AS BEGIN
declare @s nvarchar(48)
set @s=''Now''
select top 1 @s=convert(nvarchar,abs(n))+'' ''+s+case when abs(n)>1 then ''s'' else '''' end+case when n>0 then '' ago'' else '' from now'' end from (
select convert(int,(convert(float,(getdate()-@comparedate))*n)) as n, s from (
select 1/365 as n, ''Year'' as s union all
select 1/30, ''Month'' union all
select 1, ''Day'' union all
select 7, ''Week'' union all
select 24, ''Hour'' union all
select 24*60, ''Minute'' union all
select 24*60*60, ''Second''
) k
) j where abs(n)>0 order by abs(n)
return @s
END

Answer 6

Gracias por los diversos códigos publicados anteriormente.

Como Hafthor señaló, hay limitaciones del código original para hacer con el redondeo. También encontré que algunos de los resultados que arrojó su código no coincidían con lo que esperaba, por ejemplo, el viernes por la tarde -> el lunes por la mañana se mostraría como "hace 2 días". Creo que todos llamaríamos eso hace 3 días, aunque no han transcurrido 3 períodos completos de 24 horas.

Así que modifiqué el código (esto es MS SQL). Descargo de responsabilidad: soy un codificador TSQL novato, así que esto es bastante hacky, pero funciona !!

He hecho algunas anulaciones, por ejemplo, cualquier cosa de hasta 2 semanas se expresa en días. Cualquier cantidad superior a 2 meses se expresa en semanas. Todo lo demás es en meses, etc. Simplemente parecía la forma intuitiva de expresarlo.

CREATE FUNCTION [dbo].[GetFriendlyDateTimeValue]( @CompareDate DATETIME ) RETURNS NVARCHAR(48) AS BEGIN
declare @s nvarchar(48)
set @s=''Now''
select top 1 @s=convert(nvarchar,abs(n))+'' ''+s+case when abs(n)>1 then ''s'' else '''' end+case when n>0 then '' ago'' else '' from now'' end from (
select convert(int,(convert(float,(getdate()-@comparedate))*n)) as n, s from (
select 1/365 as n, ''year'' as s union all
select 1/30, ''month'' union all
select 1/7, ''week'' union all
select 1, ''day'' union all
select 24, ''hour'' union all
select 24*60, ''minute'' union all
select 24*60*60, ''second''
) k
) j where abs(n)>0 order by abs(n)
if @s like ''%days%''
BEGIN
-- if over 2 months ago then express in months
IF convert(nvarchar,DATEDIFF(MM, @CompareDate, GETDATE())) >= 2
BEGIN
select @s = convert(nvarchar,DATEDIFF(MM, @CompareDate, GETDATE())) + '' months ago''
END
-- if over 2 weeks ago then express in weeks, otherwise express as days
ELSE IF convert(nvarchar,DATEDIFF(DD, @CompareDate, GETDATE())) >= 14
BEGIN
select @s = convert(nvarchar,DATEDIFF(WK, @CompareDate, GETDATE())) + '' weeks ago''
END
ELSE
select @s = convert(nvarchar,DATEDIFF(DD, @CompareDate, GETDATE())) + '' days ago''
END
return @s
END

Answer 7

Las publicaciones anteriores me dieron algunas buenas ideas, así que aquí hay otra función para cualquiera que use SQL Server 2012.

 CREATE FUNCTION [dbo].[FN_TIME_ELAPSED]
(
@TIMESTAMP DATETIME
)
RETURNS VARCHAR(50)
AS
BEGIN
RETURN
(
SELECT TIME_ELAPSED =
CASE
WHEN @TIMESTAMP IS NULL THEN NULL
WHEN MINUTES_AGO < 60 THEN CONCAT(MINUTES_AGO, '' minutes ago'')
WHEN HOURS_AGO < 24 THEN CONCAT(HOURS_AGO, '' hours ago'')
WHEN DAYS_AGO < 365 THEN CONCAT(DAYS_AGO, '' days ago'')
ELSE CONCAT(YEARS_AGO, '' years ago'') END
FROM ( SELECT MINUTES_AGO = DATEDIFF(MINUTE, @TIMESTAMP, GETDATE()) ) TIMESPAN_MIN
CROSS APPLY ( SELECT HOURS_AGO = DATEDIFF(HOUR, @TIMESTAMP, GETDATE()) ) TIMESPAN_HOUR
CROSS APPLY ( SELECT DAYS_AGO = DATEDIFF(DAY, @TIMESTAMP, GETDATE()) ) TIMESPAN_DAY
CROSS APPLY ( SELECT YEARS_AGO = DATEDIFF(YEAR, @TIMESTAMP, GETDATE()) ) TIMESPAN_YEAR
)
END
GO

Y la implementación:

 SELECT TIME_ELAPSED = DBO.FN_TIME_ELAPSED(AUDIT_TIMESTAMP)
FROM SOME_AUDIT_TABLE