2

I'm searching for the equivalent to the function from_unixtime(bigint unixtime), which exists in Spark-SQL, in Flink-SQL.

My aim is to convert this format : 1439799094

into this format : 2015-05-18 05:43:37

Gatsby
  • 365
  • 1
  • 5
  • 17

2 Answers2

1

Just use UDFs!

https://ci.apache.org/projects/flink/flink-docs-stable/dev/table/udfs.html

Sample Usage

test.csv

creation_date|key
1535816823|1
1536392928|2
1536272308|3

EpochTimeConverter.scala

import java.time.format.DateTimeFormatter
import java.time.{Instant, LocalDateTime, ZoneId}

import org.apache.flink.table.functions.ScalarFunction

class EpochTimeConverter extends ScalarFunction {
  def eval(epochTime: Int): String = {
    // For performance, you may cache `DateTimeFormatter` in real life
    val timePattern = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
    timePattern.format(LocalDateTime.ofInstant(Instant.ofEpochSecond(epochTime), ZoneId.systemDefault()))
  }
}

UdfExample.scala

import org.apache.flink.api.scala.{ExecutionEnvironment, _}
import org.apache.flink.table.api.scala._
import org.apache.flink.table.api.{TableEnvironment, Types}
import org.apache.flink.table.sources.CsvTableSource
import org.apache.flink.types.Row

object UdfExample {
  def main(args: Array[String]): Unit = {
    val env = ExecutionEnvironment.getExecutionEnvironment
    val tableEnv = TableEnvironment.getTableEnvironment(env)
    val csvData = CsvTableSource
      .builder()
      .path("test.csv")
      .ignoreFirstLine()
      .fieldDelimiter("|")
      .field("creation_date", Types.INT)
      .field("key", Types.INT)
      .build()

    tableEnv.registerTableSource("temp_table", csvData)

    println("Without udf:")
    tableEnv.sqlQuery("SELECT creation_date, key FROM temp_table").toDataSet[Row].print()

    tableEnv.registerFunction("from_unixtime", new EpochTimeConverter())

    println()
    println("With udf:")
    tableEnv.sqlQuery("select from_unixtime(creation_date),key from temp_table").toDataSet[Row].print()
  }
}

If you run UdfExample.scala, it will produce a similar output as follows:

Without udf:
1535816823,1
1536272308,3
1536392928,2

With udf:
2018-09-01 18:47:03,1
2018-09-07 01:18:28,3
2018-09-08 10:48:48,2
veysiertekin
  • 1,731
  • 2
  • 15
  • 40
  • Thanks. A UDF isn't quite what I was hoping for though. I'm surprised there isn't an easier built in solution into the language. – hlin117 Feb 06 '19 at 00:06
-1

I think you're looking for DATE_FORMAT(timestamp, '%Y-%m-%d %H:%i:%s').

See the documentation on built-in functions and date format specifiers for more information.

David Anderson
  • 39,434
  • 4
  • 33
  • 60
  • Thanks for your reply but the original format is bigint not timestamp. And I can't cast it to timestamp (cast(myfield as timestamp)). It says "Cast function cannot convert value of type BIGINT to type TIMESTAMP(3)" – Gatsby Sep 07 '18 at 07:22
  • 1
    I'm searching for a solution for this issue also. Did anyone overcome this? – Rafi Aroch Oct 22 '18 at 12:34