I have a login table with columns UserID and login_date like below (but with thousands of entries). How would I find the longest streak of consecutive log-ins for each user and what the start and end days were for those streaks? Edit: Using Postgres. Removed question about streaks only counting M-F and not weekends (Do I make a separate post
Tag: timestamp
Want to convert timestamp to date format in hive
want to convert this number ‘20210412070422’ to date format ‘2021-04-12’ in hive I am trying but this returns null value from_unixtime(unix_timestamp(eap_as_of_dt, ‘MM/dd/yyyy’)) Answer The best methoid is to do without unix_timestamp/from_unixtime if possible and in your case it is possible. date() can be removed, string in yyyy-MM-dd format is compatible with date type: Result: Another efficient method using regexp_replace: If
How to convert a timestamp field to int8? Or just drop the column and make a new one?
I have a PostgreSQL table with a column of type timestamp. I had included this column a while ago just in case I wanted to use it for something in the future. I am now looking to convert it into an int8 and use it as an epoch time column. All rows of the table have this column set to
Set minutes of timestamp to a specific value
What is the easiest way to set the exact minutes value of a timestamp? This only adds minutes instead of setting the exact value: Answer Use date_trunc() before you add 2 minutes: Or, to retain seconds and sub-seconds: Demo: Should be substantially faster than manipulating the text representation and casting back.
insert extra rows in query result sql
Given a table with entries at irregular time stamps, “breaks” must be inserted at regular 5 min intervals ( the data associated can / will be NULL ). I was thinking of getting the start time, making a subquery that has a window function and adds 5 min intervals to the start time – but I only could think of
Hive SQL cast string as timestamp without losing the milliseconds
I have string data in the form 2020-10-21 12:49:27.090 I want to cast it as a timestamp. When I do this: select cast(column_name as timestamp) as column_name from table_name all of the milliseconds are dropped, like this: 2020-10-21 12:49:27 I also tried this: select cast(date_format(column_name,’yyyy-MM-dd HH:mm:ss.SSS’) as timestamp) as column_name from table_name and the same problem persists, it drops the
SQL timestamp filtering based only on time
I want to create a query in Oracle SQL that will grab records from a given time interval, during certain hours of the day, e.g. records between 10am to noon, in the past 10 days. I tried this, but it does not work: where timestamp is of type TIMESTAMP. I have also thought of using a join, but I am
MySQL convert timestamp and time string to DateTime format
In my table have two columns, one as timestamp to save the date and one as time string to save the time with period. Eg: I want to combine them into one column as DateTime format in the result then order by desc on that column. Here is the example: http://sqlfiddle.com/#!9/25eb21/4 The column name ‘Datetime’ expected is Datetime or timestamps
filter timestamp column in SQL Oracle
I have a table with a Timestamp column which I need to filter after ‘2020-08-26’, but every solution won’t work RUN_TIME 2020-07-22 04:22:07 2020-07-22 04:34:07 2020-07-22 04:45:07 2020-07-22 04:50:07 …
How do you I calculate the difference between two timestamps in PostgreSQL?
I have a SELECT query to retrieve data I need. One of the needed data is a an “aggregate” function to select two timestamp dates and calculate the difference between them. Using several resources and websites, they all gave me save answer and it did not work: I’m getting an error on the “@”: syntax error at or near “@”