Redshift:将 HH:MM:SS 格式的字符串字段转换为秒和向后 [英] Redshift: Conversion of string field in HH:MM:SS format to seconds and backwards

问题描述

1. 我一直在寻找一种简单的方法来将字符串字段从 HH:MM:SS 格式转换为 Redshift 中的秒，因为 TIME_TO_SEC 函数不存在.

1. I've been searching for an easy way to convert a string field from HH:MM:SS format to seconds in Redshift, since the TIME_TO_SEC function doesn't exist.

我有一个带有这种格式的字符串字段的呼叫中心数据库，HH:MM:SS.例如，如果它是 00:05:10 那么我需要将它转换为 310.我自己想出了以下方法，但必须有更好的方法:

I have a call center database with a string field in this format, HH:MM:SS. For example, if it was 00:05:10 then I would need to convert it to 310. I came up with the following on my own but there's got to be a better way:

(SPLIT_PART("HANDLE TIME", ':', 1) * 3600) + 
(SPLIT_PART(SPLIT_PART("HANDLE TIME", ':', 2),':', 1) * 60) + 
 CAST(SPLIT_PART("HANDLE TIME", ':', 3) AS INT)

一旦我总结了秒数，我需要将秒数转换回 HH:MM:SS 格式.所以 1249 会变成字符串 00:20:49.在 MYSQL 中如此简单，在 RedShift 中没有那么简单.

Once I sum up the seconds I need to convert the seconds back to the HH:MM:SS format. So 1249 would become the string 00:20:49. So simple in MYSQL, not so much in RedShift.

任何帮助将不胜感激.

推荐答案

Redshift 没有专门用于此的功能.但是，它有一个很棒的功能，叫做 UDF，它支持python，因此它将是完美的选择.

Redshift doesn't have functions specifically for this. But, it has a great feature, called UDF, which supports python, hence it will be the perfect fit.

1.要将秒转换为 hh:mm:ss(或天，hh:mm:ss)，在 redshift 中创建此 UDF -

1.To convert seconds to hh:mm:ss (or days, hh:mm:ss), create this UDF in redshift -

CREATE FUNCTION to_hhmmss (a bigint)
RETURNS character varying
STABLE
AS $$
  import datetime
  return str(datetime.timedelta(seconds=a))
$$ LANGUAGE plpythonu;

它接受一个大整数"(秒)值作为参数并返回一个字符串(hh:mm:ss 格式的时间)

It accepts an 'big integer' (seconds) value as a parameter and returns a string (time in hh:mm:ss format)

示例输出 -

db=# select to_hhmmss_1(12004);
 to_hhmmss_1
-------------
 3:20:04
(1 row)

db=# select to_hhmmss_1(120040);
  to_hhmmss_1
----------------
 1 day, 9:20:40
(1 row)

2.将 hh:mm:ss 转换为秒 -

2.To convert hh:mm:ss to seconds -

CREATE FUNCTION to_seconds (time_str varchar(20))
RETURNS integer
STABLE
AS $$
  h, m, s = time_str.split(':')
  return int(h) * 3600 + int(m) * 60 + int(s)
$$ LANGUAGE plpythonu;

此函数将接受一个字符串(时间单位为 hh:mm:ss)并返回一个整数(秒).示例输出 -

This function will accept a string (time in hh:mm:ss) and return an integer (seconds). Example Output -

db=# select to_seconds('1:00:00');
 to_seconds
------------
       3600
(1 row)

这篇关于Redshift:将 HH:MM:SS 格式的字符串字段转换为秒和向后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！