logo
down
shadow

How can I convert an integer representing EPOCH time to a timestamp in Athena (Presto)?


How can I convert an integer representing EPOCH time to a timestamp in Athena (Presto)?

By : Khurram Arshad
Date : October 17 2020, 06:10 AM
will help you You can use from_unixtime():
code :
presto> select from_unixtime(1556895150);
          _col0
-------------------------
 2019-05-03 07:52:30.000
(1 row)


Share : facebook icon twitter icon
How to convert a timestamp to an integer (Unix epoch) in Postgres

How to convert a timestamp to an integer (Unix epoch) in Postgres


By : Howie Young
Date : March 29 2020, 07:55 AM
this one helps. I have a string "2016-10-25T00:14:30.000" in PostgreSQL.
code :
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2016-10-25T00:14:30.000');

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2016-10-25T00:14:30.000' + INTERVAL '544 min');
How to convert timestamp string to epoch time?

How to convert timestamp string to epoch time?


By : Alejandro Lorite
Date : March 29 2020, 07:55 AM
I wish this help you Note: originally the question had the input 2017-18-08 12:60:30.345 (with 60 in the minutes field), then it was edited (the time changed from 12:60 to 11:45), but I decided to keep this answer discussing about the original input (12:60), as it also works for the edited version (11:45).
code :
// change 60 minutes to 59 (otherwise it doesn't work)
String timeDateStr = "2017-18-08 12:59:30.345";
DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-dd-MM HH:mm:ss.SSS");
// parse to LocalDateTime
LocalDateTime dt = LocalDateTime.parse(timeDateStr, dtf);

// assume the LocalDateTime is in UTC
Instant instant = dt.toInstant(ZoneOffset.UTC);
System.out.println(instant.toEpochMilli());
// get the LocalDateTime in some timezone
ZonedDateTime z = dt.atZone(ZoneId.of("Europe/London"));
System.out.println(z.toInstant().toEpochMilli());
// get the LocalDateTime in +03:00 offset
System.out.println(dt.toInstant(ZoneOffset.ofHours(3)).toEpochMilli());
String timeDateStr = "2017-18-08 12:60:30.345";
DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-dd-MM HH:mm:ss.SSS")
    // use lenient parsing
    .withResolverStyle(ResolverStyle.LENIENT);
// parse to LocalDateTime
LocalDateTime dt = LocalDateTime.parse(timeDateStr, dtf);
ZoneId zone = ZoneId.of("America/Sao_Paulo");

// October 15th 2017 at midnight, DST starts in Sao Paulo
LocalDateTime d = LocalDateTime.of(2017, 10, 15, 0, 0, 0, 0);
ZonedDateTime z = d.atZone(zone);
System.out.println(z);// adjusted to 2017-10-15T01:00-02:00[America/Sao_Paulo]
// February 18th 2018 at midnight, DST ends in Sao Paulo
// local times from 23:00 to 23:59 at 17th exist twice
LocalDateTime d = LocalDateTime.of(2018, 2, 17, 23, 0, 0, 0);
// by default, it gets the offset before DST ends
ZonedDateTime beforeDST = d.atZone(zone);
System.out.println(beforeDST); // before DST end: 2018-02-17T23:00-02:00[America/Sao_Paulo]

// get the offset after DST ends
ZonedDateTime afterDST = beforeDST.withLaterOffsetAtOverlap();
System.out.println(afterDST); // after DST end: 2018-02-17T23:00-03:00[America/Sao_Paulo]
Convert DateString with offset to a valid Date/Timestamp in Athena / Presto

Convert DateString with offset to a valid Date/Timestamp in Athena / Presto


By : user2714964
Date : March 29 2020, 07:55 AM
To fix the issue you can do Use from_iso8601_timestamp:
code :
presto> select from_iso8601_timestamp('2016-09-21T10:01:43-04:00');
             _col0
--------------------------------
 2016-09-21 10:01:43.000 -04:00
PHP convert this timestamp to epoch/unix time (int)

PHP convert this timestamp to epoch/unix time (int)


By : user3080517
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , PHP DateTime can only work with 4 digits after the decimal point. So one alternative for you can be to ignore that part to begin with, then - if applicable - append it back to the final timestamp. Keep in mind though, that this can cause issues further down the line.
code :
$string = "2019-08-10D00:00:03.712125000";
$parts = explode(".", $string);
$microseconds = $parts[1];

$date = DateTime::createFromFormat("Y-m-d\DH:i:s", $parts[0]);
$timestamp = $date->getTimestamp();

// If you want to round it to 4 decimal places
// $microseconds = ltrim(round("0.".$microseconds, 4), "0.");

// If you want to append the microseconds back for whichever reason
// $timestamp .= ".".$microseconds;

echo $timestamp;
How to convert string into timestamp in Presto (Athena)?

How to convert string into timestamp in Presto (Athena)?


By : pooyan adibi
Date : March 29 2020, 07:55 AM
this one helps. Try to use from_iso8601_timestamp. Please visit below address to learn more about timestamp related functions: https://docs.starburstdata.com/latest/functions/datetime.html
code :
presto:tiny> select from_iso8601_timestamp('2018-03-27T00:20:00.855556Z');
            _col0
-----------------------------
 2018-03-27 00:20:00.855 UTC
(1 row)
select * from tb_name where elb_status_code like '5%%' AND 
from_iso8601_timestamp(date) between DATE_ADD('hour',-2,NOW()) AND NOW(); 
Related Posts Related Posts :
  • T SQL Select only first record for each day
  • SQL group and order
  • How can convert 15000000.00 to 15,000,000 in SQL server?
  • Can you tell me what this SQL would do?
  • AND vs OR in T-SQL
  • Slowness at Indexed View for SQL 2005
  • check for commonly used passwords on SQL Server
  • Find average revenue by each subcategory of top 5 categories in terms of quantity sold in vba sql
  • Improve performance of multiple insert and update queries
  • Column as a subquery
  • How to schedule a google-bigQuery so its results would import to spreadsheet on a daily basis?
  • ALTER TABLE takes forever even for a very little table
  • how to convert/calculate nvarchar to int
  • Spooky query behavior : Same query, different results when inside a view
  • Converting varchar to numeric nightmare
  • How to exclude certain combination pattern in data set?
  • Get results with wildcard
  • How to only select the SQL row with the MAX id in this join?
  • Converting a String with Length of Time into Different DataType
  • what does square brackets in an sql update statement mean?
  • CASE on WHERE CLAUSE SQL
  • Replacing value inside column
  • Weekly Schedule with SQL
  • How to choose minimum value row in Group By sql
  • Creating empty integer/decimal column by using Select
  • SQL string driving me crazy
  • summarize dates from a list of dates
  • Last 10 weeks in SQL Server
  • How to store less than / greater than in database
  • find number that is not 0 in a varchar sequence like 0000000001 or 00000123131
  • SQL query to fill multiple needs with partial quantities by using Oracle SQL window functions
  • SQL Server line break for CSV
  • I need to select clients who have multiple records in one column
  • How to group database column?
  • Getting a specific output from 2 tables
  • How to query different tables for data in order
  • About subquery in T-SQL
  • Memory Buffer pool taken by a Table
  • SQL : ORA-00906: missing left parenthesis
  • Data Conversion error while inserting into a new table from a joint statement
  • How to do sql pivot with multiple values in single column
  • Multiple rows into columns with column values group by columns in SQL Server
  • Data of previous quarters based on sysdate
  • Rearrange Date filter in stored procedure
  • ORA-00942: table or view does not exist .or We are not able to drop
  • SQL Where date1 is 3 days bigger than date2
  • #1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' in MySQL
  • Can I SELECT *, COUNT(*) in the same query?
  • PLS-00642: local collection types not allowed in SQL statements. i am using oracle datadase 12c
  • Find the One Random Set_id from each unique Skill_id
  • SQL ORACLE ORA-00923: FROM keyword not found where expected
  • implement a manual increment with just simple SQL
  • SYSDATETIMEOFFSET() to turn off the offset
  • If I query from V$SQL view, does it include SQL that are still running and not yet finished?
  • Is there a way to join results from two queries in Python using sqlalchemy engine?
  • What is the query to choose the number of people living at each address?
  • Filter By date with format 'dd/mm'
  • Listagg in query having aliases
  • SQL stored procedure error about int while there is no int
  • Selecting columns from string
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk