[FIXED] How can I subtract two date time values in a Pandas Dataframe

Issue

I’m working on a project and I need to subtract two date time values to get the number of days. A sample of the data can be seen below:

                   ALARM_DATE               CONT_DATE  \
0      2020/06/18 00:00:00+00  2020/06/23 00:00:00+00   
1      2020/06/01 00:00:00+00  2020/06/04 00:00:00+00   
2      2020/08/10 00:00:00+00  2020/03/01 00:00:00+00   
3      2020/03/31 00:00:00+00  2020/04/01 00:00:00+00   
4      2020/04/14 00:00:00+00  2020/04/19 00:00:00+00   
...                       ...                     ...   

I tried simply subtracting the values, but obviously that didn’t work. Can anyone please help?

Solution

Convert your columns to actual dates first:

df['ALARM_DATE'] = pd.to_datetime(df['ALARM_DATE'])
df['CONT_DATE'] = pd.to_datetime(df['CONT_DATE'])

Or:

df[['ALARM_DATE', 'CONT_DATE']] = df[['ALARM_DATE', 'CONT_DATE']].apply(pd.to_datetime)

Output:

>>> df['CONT_DATE'] - df['ALARM_DATE']
0      5 days
1      3 days
2   -162 days
3      1 days
4      5 days
dtype: timedelta64[ns]

Answered By - user17242583

Answer Checked By - Clifford M. (PHPFixing Volunteer)

Read More

[FIXED] How to return named tuples in C#?

Issue

I have a property that returns two items of type DateTime. When returning these values I have to reference them as Item1 and Item2. How do I return with custom names e.g.

filter?.DateRanges.From
filter?.DateRanges.To

 

public Tuple<DateTime, DateTime> DateRanges
{
    get
    {
        From = DateTime.Now.AddDays(-1).Date.AddMonths(-1);
        To = DateTime.Now.AddDays(-1).Date;

        if (Preset != 0)
        {
            if (Preset == DatePreset.SpecificRange)
            {
                From = From.Date;
                To = To.Date;
            }
            else
            {
                var dateRange = DateTime.Today.AddDays(-1).GetDateRangeByPreset(Preset);
                From = dateRange.From;
                To = dateRange.To;
            }
        }

        return new Tuple<DateTime, DateTime>(From, To);
    }

usage:

var from = filter?.DateRanges.Item1;
var to = filter?.DateRanges.Item2;

Solution

Like this:

public (DateTime Start, DateTime End) DateRanges
{
    get
    {
        return (DateTime.MinValue, DateTime.MaxValue);
    }
}

Note: This requires a recent version of C# and .Net.

Incidentally, watch out for this usage pattern:

var from = filter?.DateRanges.Start;
var to = filter?.DateRanges.End;

That's inefficient because it causes two identical tuples to be created.

This is better:

var range = filter?.DateRanges;

if (range.HasValue)
{
    var from  = range.Value.Start;
    var to    = range.Value.End;
}

However note that tuples cannot be null (they are value types) so you could write it like so:

if (filter != null)
{
    var range = filter.DateRanges;
    var from  = range.Start;
    var to    = range.End;
    ...
}

---

ADDENDUM (2022):

Nowadays you can much more simply assign the values of a tuple to local variables. Now we can rewrite the last example above like so:

if (filter != null)
{
    var (from, to) = filter.DateRanges;
    ...
}

Answered By - Matthew Watson

Answer Checked By - Cary Denson (PHPFixing Admin)

Read More

[FIXED] Why is Node.js fs.statSync birthtime in Node.js off?

Issue

I have two questions that I haven't seen answers for on this matter, nor do I recall a heads up in the docs.

1. Why are fs.statSync datetime stamps such as birthtime all off and not matching what I see when I view the files manually in my OS (which shows the correct datetimes)?

2. How do I get the EXACT correct times, exactly as they are shown on the file itself? I know the dates and times are correct when I view it in the OS because I was there for the photo. Just don't know what to do to make Node.js give me the times as they really were/are.

Here's my correct output in the OS (Windows in this case, but it needs to work the same in all OSs):

Here's the call I'm making within seconds of the other:

const stats = fs.statSync(absPathOfFile);
const dates = [
    stats.birthtime,
    stats.ctime,
    stats.mtime,
    stats.atime,
];

And that Node.js output:

[
  2022-10-31T08:47:00.900Z,
  2022-06-13T05:37:42.128Z,
  2022-04-12T04:55:49.070Z,
  2022-10-31T08:47:02.027Z
]

So all of those dates are off. I was expecting Node.js to return the actual dates/times as listed in the file's meta data.

Solution

To make the comment discussion an answer:

• birthtime is not necessarily available on all file systems, and how the atime/mtime/ctime/birthtime fields map to file system properties depends. Node.js docs here.
• The "Date Taken" that Windows (and maybe other OSes) shows is Exif metadata pulled from the file's internal data itself, and is not related to a filesystem date. (IOW, moving the file around or resetting its times with e.g. touch or other calls doesn't affect it.)
• The dates returned by statSync are in UTC time (which is evident from the Z time zone specifier in the ISO8601 formatted output). Windows shows the dates in the user's local time zone.

Answered By - AKX

Answer Checked By - Willingham (PHPFixing Volunteer)

Read More

[FIXED] How to join generated datetimes with data in SQLite database?

Issue

I have a table with data from a sensor created like that:

CREATE TABLE IF NOT EXISTS "aqi" (
  "time" datetime,
  "pm25" real,
  "pm10" real
);

When is sensor running, it sends data to a server (which it writes to a database) every second. But when the sensor is not running, there are "gaps" in data in the database like that (I've rewritten time column to a readable format and timezone GMT+01, leaving raw data in parentheses):

time	pm25	pm10
...	...	...
2021-12-28 18:44 (1640713462)	9.19	9.27
2021-12-28 18:45 (1640713522)	9.65	9.69
2021-12-28 18:46 (1640713582)	9.68	9.76
2021-12-29 10:17 (1640769421)	7.42	7.42
2021-12-29 10:18 (1640769481)	7.94	7.98
2021-12-29 10:19 (1640769541)	7.42	7.43
...	...	...

---

I wanted to create a query, that selects data from the last 24 hours, outputting pm25 and pm10 as NULL if there aren't data in the table for the current time. So the table above would look like that:

time	pm25	pm10
...	...	...
2021-12-28 18:44 (1640713462)	9.19	9.27
2021-12-28 18:45 (1640713522)	9.65	9.69
2021-12-28 18:46 (1640713582)	9.68	9.76
2021-12-28 18:47 (1640713642)	NULL	NULL
2021-12-28 18:48 (1640713702)	NULL	NULL
2021-12-28 18:49 (1640713762)	NULL	NULL
...	...	...
2021-12-29 10:14 (1640769262)	NULL	NULL
2021-12-29 10:15 (1640769322)	NULL	NULL
2021-12-29 10:16 (1640769382)	NULL	NULL
2021-12-29 10:17 (1640769421)	7.42	7.42
2021-12-29 10:18 (1640769481)	7.94	7.98
2021-12-29 10:19 (1640769541)	7.42	7.43
...	...	...

_{^{I don't mind if the seconds would be different because of the generation of time...}}

---

I tried generating time for the last 24 hours using code from https://stackoverflow.com/a/32987070 and that works, as I wanted:

WITH RECURSIVE dates(generated_time) AS (
  VALUES(datetime('now', '-1 minute', 'localtime'))
  UNION ALL
  SELECT datetime(generated_time, '-1 minute')
  FROM dates
  LIMIT 1440
)
SELECT strftime('%Y-%m-%d %H:%M', datetime(generated_time)) AS time
FROM dates;

But I don't know how to add (JOIN) data from the sensor (columns pm25, pm10) to query above... I tried something, but it outputs 0 rows:

WITH RECURSIVE dates(generated_time) AS (
  VALUES(datetime('now', '-1 minute', 'localtime'))
  UNION ALL
  SELECT datetime(generated_time, '-1 minute')
  FROM dates
  LIMIT 1440
)
SELECT
    strftime('%Y-%m-%d %H:%M', datetime(generated_time)) AS generated_time,
    pm25, 
    pm10
FROM
    dates
    INNER JOIN aqi ON generated_time = strftime('%Y-%m-%d %H:%M', datetime(aqi.time));

Probably it's something really obvious, that I'm missing, but I have no idea :/

---

EDIT:
As @DrummerMann pointed out, it works with LEFT JOIN, but it takes around one whole minute to execute the query (in the database is around 14 000 values):

WITH RECURSIVE dates(time) AS (
  VALUES(datetime('now', '-1 minute', 'localtime'))
  UNION ALL
  SELECT datetime(time, '-1 minute')
  FROM dates
  LIMIT 1440
)
SELECT
    dates.time,
    aqi.pm25, 
    aqi.pm10
FROM
    dates
    LEFT JOIN aqi ON strftime('%Y-%m-%d %H:%M', datetime(dates.time)) = strftime('%Y-%m-%d %H:%M', datetime(aqi.time, 'unixepoch', 'localtime'))
    ORDER BY dates.time;

Is there any better way to do that?

Solution

Try this version of the cte, which uses integer unix timestamps where the seconds are stripped off and there are no functions in the ON clause of the join:

WITH RECURSIVE dates(generated_time) AS (
  SELECT strftime('%s', 'now', '-1 minute', 'localtime') / 60 * 60
  UNION ALL
  SELECT generated_time - 60
  FROM dates
  LIMIT 1440
)
SELECT strftime('%Y-%m-%d %H:%M', d.generated_time, 'unixepoch', 'localtime') AS generated_time,
       a.pm25, 
       a.pm10
FROM dates d LEFT JOIN aqi a
ON d.generated_time = a.time / 60 * 60;

Answered By - forpas

Answer Checked By - Gilberto Lyons (PHPFixing Admin)

Read More

[FIXED] How to Add "Z" at the end of date string with moment in javascript and nodejs

Issue

I want to format my date such that the "Z" letter appears at the end of the date.

I've tried many things but nothing seems to be working. i need it to be in the format of "YYYY-MM-DDT00:00:00.000Z" right now it is in this format except for the Z

How can I include the Z at the end using moment, note that i need the date at the start of the day, as in everything after the "T" is 0.

My Code:

   console.log(moment(req.body.to, "YYYY-MM-DD").startOf('day'))

   'from': {$eq:  moment(req.body.from, "YYYY-MM-DD").startOf('day')},

output of the log:

(moment("2022-10-09T00:00:00.000"))

Solution

Taking from the docs you could do:

moment(req.body.to, "YYYY-MM-DD").startOfDay().format("YYYY-MM-DD[T]HH:mm:ss.SSS[Z]")

Escaping characters To escape characters in format strings, you can wrap the characters in square brackets.

Or, since you only want zeroes:

moment(req.body.to, "YYYY-MM-DD").format("YYYY-MM-DD[T00:00:00.000Z]")

Or, since your example indicates that your date is already in YYYY-MM-DD format, why not just do:

`${req.body.to}T00:00:00.000Z`

Answered By - Emil Hernqvist

Answer Checked By - David Marino (PHPFixing Volunteer)

Read More

[FIXED] How can set format datetime [PHPExcel]

Issue

set format datetime with php and output excel

date_default_timezone_set('Asia/Bangkok');
$objPHPExcel->getActiveSheet()->SetCellValue('S1', 'Show DateTime');
$dateValue = PHPExcel_Shared_Date::PHPToExcel( strtotime('23-Apr-1989 17:05:50') );
$objPHPExcel->getActiveSheet()
    ->setCellValue('S2', $dateValue);
$objPHPExcel->getActiveSheet()
    ->getStyle('S2')
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

but output

How can show data on Excel 23/04/1989 17:05:50 and excel type Date

Solution

You currently use the predefined constant PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH for your number format.

If you replace that by your desired format, everything should be ok: 'dd/mm/yyyy hh:mm:ss'

Also see the documentation for setFormatCode(): http://apigen.juzna.cz/doc/ouardisoft/PHPExcel/class-PHPExcel_Style_NumberFormat.html#_setFormatCode

Answered By - Leviathan

Answer Checked By - Candace Johnson (PHPFixing Volunteer)

Read More

[FIXED] How to check date type in a variable in PHP

Issue

I have an array in which dates are likes 01/10/2015 22:53:00 or 08/19/2016. i want to check that whether it is a date or not. If somebody put a text or other type in the field(Excel array), which i am putting into a variable, then it should generate an error that this is not a date.

I have searched but did not found success. Any help would be appreciated.

Solution

Try this (simple preg_match):

//your date
$date = '01/10/2015 12:12:23';

//check format
if(preg_match("/^[0-9]{1,2}\\/[0-9]{2}\\/[0-9]{4}$/", $date) OR preg_match("/^[0-9]{1,2}\\/[0-9]{2}\\/[0-9]{4} [0-9]{2}\\:[0-9]{2}\\:[0-9]{2}$/", $date)) {
    echo 'OK';
}else {
    echo 'BAD';
}

Answered By - Arkadiusz G.

Answer Checked By - Katrina (PHPFixing Volunteer)

Read More

[FIXED] How to get current date and time from GPS unsegment time in python

Issue

I have gps unsegmented time like this:

Tgps = 1092121243.0

And I'd like to understand what date and time is that. The begining of GPS time is 6 January 1980. Python function

datetime.utcfromtimestamp 

could give seconds from 1 January 1970 year.

I found following:

from datetime import datetime
GPSfromUTC = (datetime(1980,1,6) - datetime(1970,1,1)).total_seconds()
curDate = datetime.utcfromtimestamp(Tgps + GPSfromUTC) 

Out[83]: datetime.datetime(2014, 8, 15, 7, 0, 43)

I'm not sure about leapseconds are they included in function datetime or I should calculate them and substract from the result? May be also exists better solution of this problem?

Solution

GPS time started in sync with UTC: 1980-01-06 (UTC) == 1980-01-06 (GPS). Both tick in SI seconds. The difference between GPS time and UTC time increases with each (intercalary) leap second.

To find the correct UTC time, you need to know the number of leap seconds occurred before the given GPS time:

#!/usr/bin/env python
from datetime import datetime, timedelta

# utc = 1980-01-06UTC + (gps - (leap_count(2014) - leap_count(1980)))
utc = datetime(1980, 1, 6) + timedelta(seconds=1092121243.0 - (35 - 19))
print(utc)

Output

2014-08-15 07:00:27 # (UTC)

where leap_count(date) is the number of leap seconds introduced before the given date. From TAI-UTC table (note: the site is the authoritative source on leap seconds. It publishes Bulletin C announcing new leap seconds):

1980..: 19s 
2012..: 35s

and therefore:

(leap_count(2014) - leap_count(1980)) == (35 - 19)

---

If you are on Unix then you could use "right" time zone to get UTC time from TAI time (and it is easy to get TAI time from GPS time: TAI = GPS + 19 seconds (constant offset)):

#!/usr/bin/env python
import os
import time

os.environ['TZ'] = 'right/UTC' # TAI scale with 1970-01-01 00:00:10 (TAI) epoch
time.tzset() # Unix

from datetime import datetime, timedelta

gps_timestamp = 1092121243.0 # input
gps_epoch_as_gps = datetime(1980, 1, 6) 
# by definition
gps_time_as_gps = gps_epoch_as_gps + timedelta(seconds=gps_timestamp) 
gps_time_as_tai = gps_time_as_gps + timedelta(seconds=19) # constant offset
tai_epoch_as_tai = datetime(1970, 1, 1, 0, 0, 10)
# by definition
tai_timestamp = (gps_time_as_tai - tai_epoch_as_tai).total_seconds() 
print(datetime.utcfromtimestamp(tai_timestamp)) # "right" timezone is in effect!

Output

2014-08-15 07:00:27 # (UTC)

---

You could avoid changing the timezone if you extract the leap seconds list from the corresponding tzfile(5). It is a combination of the first two methods where the leap count computation from the first method is automated and the autoupdating tzdata (system package for the tz database) from the second method is used:

>>> from datetime import datetime, timedelta
>>> import leapseconds
>>> leapseconds.gps_to_utc(datetime(1980,1,6) + timedelta(seconds=1092121243.0))
datetime.datetime(2014, 8, 15, 7, 0, 27)

where leapseconds.py can extract leap seconds from /usr/share/zoneinfo/right/UTC file (part of tzdata package).

All three methods produce the same result.

Answered By - jfs

Answer Checked By - Willingham (PHPFixing Volunteer)

Read More

[FIXED] How do I get a SystemTime from a human-readable date format in a cross-platform way? (Rust)

Issue

How would I convert a human readable time (in any format, such as Tue, 1 Jul 2003 10:52:37 +0200) into a SystemTime, in a cross-platform way? I know about chrono::DateTime::from_rfc2822(), but I've been searching for quite a while and I can't find a way to convert a DateTime into a SystemTime. This conversion also needs to be cross-platform, so I can't use the platform-specific epochs (such as UNIX_EPOCH).

Does anyone have any advice or ideas on how to do this?

Solution

There is a conversion available for DateTime<Tz> to SystemTime, so you just need to call .into():

let system_time: SystemTime = DateTime::parse_from_rfc2822("Tue, 1 Jul 2003 10:52:37 +0200").unwrap().into();

Playground

Answered By - kmdreko

Answer Checked By - Robin (PHPFixing Admin)

Read More

[FIXED] How to get days, hours, minutes, seconds from DateTime as a decimal?

Issue

I have a DateTime and need to get the days and hours in a decimal format. For example, for new DateTime(2009, 6, 19, 18, 0, 0); I need the days as 19.75. Day + (TimeOfDay.TotalHours / 24) seems to work but is there a straight or better conversion? Speed is important for this calculation considering I'm also using high-resolution dates.

Solution

Given...

DateTime time = new DateTime(2009, 6, 19, 18, 0, 0);

...what you have...

decimal totalDays1 = (decimal) (time.Day + time.TimeOfDay.TotalHours / 24);// 19.75M

...is already pretty terse, so I don't know how much you're wanting or expecting to improve that. You could use the TimeSpan.TotalDays property, but it takes a bit more work to set that up...

DateTime lastDayOfPreviousMonth = new DateTime(time.Year, time.Month, 1).AddDays(-1);
decimal totalDays2 = (decimal) (time - lastDayOfPreviousMonth).TotalDays;// 19.75M

I used BenchmarkDotNet to benchmark four different methods...

using System;
using BenchmarkDotNet.Attributes;

public static class Program
{
    static void Main(string[] args)
    {
        BenchmarkDotNet.Running.BenchmarkRunner.Run<CalculateTotalDaysBenchmarks>();
    }
}

[ClrJob()]
[CoreJob()]
public class CalculateTotalDaysBenchmarks
{
    private static readonly DateTime TestTime = new DateTime(2009, 6, 19, 18, 0, 0);

    [Benchmark(Baseline = true)]
    public decimal Method1_DayPlusTotalHoursDivided_CastResult()
    {
        return (decimal) (TestTime.Day + TestTime.TimeOfDay.TotalHours / 24);
    }

    [Benchmark()]
    public decimal Method1_DayPlusTotalHoursDivided_CastTotalHours()
    {
        return TestTime.Day + (decimal) TestTime.TimeOfDay.TotalHours / 24;
    }

    [Benchmark()]
    public decimal Method2_DayPlusTicksDivided()
    {
        return TestTime.Day + (decimal) TestTime.TimeOfDay.Ticks / TimeSpan.TicksPerDay;
    }

    [Benchmark()]
    public decimal Method3_SubtractLastDayOfPreviousMonth()
    {
        DateTime lastDayOfPreviousMonth = new DateTime(TestTime.Year, TestTime.Month, 1).AddDays(-1);

        return (decimal) (TestTime - lastDayOfPreviousMonth).TotalDays;
    }

    [Benchmark()]
    public decimal Method4_NewTimeSpan()
    {
        return (decimal) new TimeSpan(TestTime.Day, TestTime.Hour, TestTime.Minute, TestTime.Second, TestTime.Millisecond).TotalDays;
    }
}

...and got these results...

// * Summary *

BenchmarkDotNet=v0.11.4, OS=Windows 10.0.17763.379 (1809/October2018Update/Redstone5)
Intel Core i7 CPU 860 2.80GHz (Nehalem), 1 CPU, 8 logical and 4 physical cores
.NET Core SDK=2.1.505
  [Host] : .NET Core 2.1.9 (CoreCLR 4.6.27414.06, CoreFX 4.6.27415.01), 64bit RyuJIT
  Clr    : .NET Framework 4.7.2 (CLR 4.0.30319.42000), 64bit RyuJIT-v4.7.3362.0
  Core   : .NET Core 2.1.9 (CoreCLR 4.6.27414.06, CoreFX 4.6.27415.01), 64bit RyuJIT


|                                          Method | Runtime |     Mean |     Error |    StdDev | Ratio | RatioSD |
|------------------------------------------------ |-------- |---------:|----------:|----------:|------:|--------:|
|     Method1_DayPlusTotalHoursDivided_CastResult |     Clr | 118.2 ns | 1.2644 ns | 1.1827 ns |  1.00 |    0.00 |
| Method1_DayPlusTotalHoursDivided_CastTotalHours |     Clr | 263.9 ns | 0.7289 ns | 0.6462 ns |  2.23 |    0.02 |
|                     Method2_DayPlusTicksDivided |     Clr | 194.1 ns | 0.8827 ns | 0.8256 ns |  1.64 |    0.02 |
|          Method3_SubtractLastDayOfPreviousMonth |     Clr | 138.9 ns | 0.4757 ns | 0.3714 ns |  1.17 |    0.01 |
|                             Method4_NewTimeSpan |     Clr | 134.7 ns | 0.8376 ns | 0.7835 ns |  1.14 |    0.01 |
|                                                 |         |          |           |           |       |         |
|     Method1_DayPlusTotalHoursDivided_CastResult |    Core | 113.3 ns | 0.1982 ns | 0.1655 ns |  1.00 |    0.00 |
| Method1_DayPlusTotalHoursDivided_CastTotalHours |    Core | 261.3 ns | 2.9683 ns | 2.6313 ns |  2.31 |    0.02 |
|                     Method2_DayPlusTicksDivided |    Core | 197.9 ns | 4.4254 ns | 5.2681 ns |  1.74 |    0.04 |
|          Method3_SubtractLastDayOfPreviousMonth |    Core | 131.1 ns | 0.8406 ns | 0.7863 ns |  1.16 |    0.01 |
|                             Method4_NewTimeSpan |    Core | 132.1 ns | 1.1211 ns | 1.0486 ns |  1.16 |    0.01 |

What you started with is appreciably faster than the other methods.

Answered By - Lance U. Matthews

Answer Checked By - David Marino (PHPFixing Volunteer)

Read More

[FIXED] How to Convert DateTime.Now in 0 to 1 decimal format in c#

Issue

I would like to convert the current time to a decimal representing a fraction of the day. For example, if the day starts at 0, then 12:00 PM should be 0.5.

I need to send that value to an API, and it needs to be in that format. i.e.

"LAST_PRINT_TIME":0.22020833"

Solution

Depending on the precision requirements of your result, this may help you:

DateTime now = DateTime.Now;
double dayFraction = (now.Hour + now.Minute / 60d) / 24d;

now.Minute / 60d calculates the fraction of the current hour (so if the time is XX:15 PM this will give 0.25). This is then added to the current hour. This value is then divided by 24 to obtain the final result.

For example, 3:45 PM would go as follows: (15 + 45 / 60) / 24) => (15 + 0.75) / 24 => 15.75 / 24 => 0.65625

So 3:45 PM, which is 15.75 hours into the day, would be 0.65625 (or 65.625%) of the day.

---

Or, as @madreflection mentioned in a comment, you could use .ToOADate() as well. In this case, you could do something like:

DateTime now = DateTime.Now;
double dayFraction = now.ToOADate() - now.Date.ToOADate();

Answered By - elmer007

Answer Checked By - Robin (PHPFixing Admin)

Read More

[FIXED] How to convert decimal value to 24 hour time format in JAVA

Issue

I am retrieving time values from SQLServer which are displayed as 0.0833333333333333, while it originally is 02:00 in 24 hour format. Now, I need to convert this decimal value 0.0833333333333333 to 02:00 to do further coding. Is there any direct/simple way to do it in JAVA?

Solution

Here is an example:

double d = 0.0833333333333333;
Date date = new Date(Math.round(d * 24L * 60L * 60L * 1000L));
SimpleDateFormat format = new SimpleDateFormat("HH:mm");
format.setTimeZone(TimeZone.getTimeZone("UTC"));
System.out.println(format.format(date));

Answered By - Marcio Lucca

Answer Checked By - David Marino (PHPFixing Volunteer)

Read More

[FIXED] How to calculate difference in hours (decimal) between two dates in SQL Server?

Issue

I have to calculate the difference in hours (decimal type) between two dates in SQL Server 2008.

I couldn't find any useful technique to convert datetime to decimal with 'CONVERT' on MSDN.
Can anybody help me with that?

UPDATE:
To be clear, I need the fractional part as well (thus decimal type). So from 9:00 to 10:30 it should return me 1.5.

Solution

DATEDIFF(hour, start_date, end_date) will give you the number of hour boundaries crossed between start_date and end_date.

If you need the number of fractional hours, you can use DATEDIFF at a higher resolution and divide the result:

DATEDIFF(second, start_date, end_date) / 3600.0

The documentation for DATEDIFF is available on MSDN:

http://msdn.microsoft.com/en-us/library/ms189794%28SQL.105%29.aspx

Answered By - Phil Ross

Answer Checked By - Terry (PHPFixing Volunteer)

Read More

[FIXED] How to add Current Date and Time into Table?

Issue

how can I add the current date and time with this format: 2022-07-17 17:50:20?

I already managed it with an input value but I need the current time.

Solution

You should use built-in Date object in Javascript, like this:

let currentdate = new Date(); 
let datetime = currentdate.getFullYear() + "-"
                + String((currentdate.getMonth() + 1)).padStart(2,"0")  + "-" 
                + String(currentdate.getDate()).padStart(2,"0") + " "
                + String(currentdate.getHours()).padStart(2,"0") + ":"  
                + String(currentdate.getMinutes()).padStart(2,"0") + ":" 
                + String(currentdate.getSeconds()).padStart(2,"0");
//The `+1` on the getMonth() is used because in Javascript January is 0.
console.log(datetime);

By the way, I suggest you make all your vars into lets as that's the current standard. There's a very fine difference between them (let is block scoped while var is functional scoped).

Answered By - TheBooker66 aka Ethan

Answer Checked By - Clifford M. (PHPFixing Volunteer)

Read More

[FIXED] How to Validate a DateTime in C#?

Issue

I doubt I am the only one who has come up with this solution, but if you have a better one please post it here. I simply want to leave this question here so I and others can search it later.

I needed to tell whether a valid date had been entered into a text box and this is the code that I came up with. I fire this when focus leaves the text box.

try
{
    DateTime.Parse(startDateTextBox.Text);
}
catch
{
    startDateTextBox.Text = DateTime.Today.ToShortDateString();
}

Solution

DateTime.TryParse

This I believe is faster and it means you dont have to use ugly try/catches :)

e.g

DateTime temp;
if(DateTime.TryParse(startDateTextBox.Text, out temp))
{
  // Yay :)
}
else
{
  // Aww.. :(
}

Answered By - Chris James

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)

Read More

[FIXED] How to convert datetime to integer in python

Issue

How can I convert YYYY-MM-DD hh:mm:ss format to integer in python? for example 2014-02-12 20:51:14 -> to integer.

I only know how to convert hh:mm:ss but not yyyy-mm-dd hh:mm:ss

def time_to_num(time_str):
    hh, mm , ss = map(int, time_str.split(':'))
    return ss + 60*(mm + 60*hh)

Solution

It depends on what the integer is supposed to encode. You could convert the date to a number of milliseconds from some previous time. People often do this affixed to 12:00 am January 1 1970, or 1900, etc., and measure time as an integer number of milliseconds from that point. The datetime module (or others like it) will have functions that do this for you: for example, you can use int(datetime.datetime.utcnow().timestamp()).

If you want to semantically encode the year, month, and day, one way to do it is to multiply those components by order-of-magnitude values large enough to juxtapose them within the integer digits:

2012-06-13 --> 20120613 = 10,000 * (2012) + 100 * (6) + 1*(13)

def to_integer(dt_time):
    return 10000*dt_time.year + 100*dt_time.month + dt_time.day

E.g.

In [1]: import datetime

In [2]: %cpaste
Pasting code; enter '--' alone on the line to stop or use Ctrl-D.
:def to_integer(dt_time):
:    return 10000*dt_time.year + 100*dt_time.month + dt_time.day
:    # Or take the appropriate chars from a string date representation.
:--

In [3]: to_integer(datetime.date(2012, 6, 13))
Out[3]: 20120613

If you also want minutes and seconds, then just include further orders of magnitude as needed to display the digits.

I've encountered this second method very often in legacy systems, especially systems that pull date-based data out of legacy SQL databases.

It is very bad. You end up writing a lot of hacky code for aligning dates, computing month or day offsets as they would appear in the integer format (e.g. resetting the month back to 1 as you pass December, then incrementing the year value), and boiler plate for converting to and from the integer format all over.

Unless such a convention lives in a deep, low-level, and thoroughly tested section of the API you're working on, such that everyone who ever consumes the data really can count on this integer representation and all of its helper functions, then you end up with lots of people re-writing basic date-handling routines all over the place.

It's generally much better to leave the value in a date context, like datetime.date, for as long as you possibly can, so that the operations upon it are expressed in a natural, date-based context, and not some lone developer's personal hack into an integer.

Answered By - ely

Answer Checked By - Katrina (PHPFixing Volunteer)

Read More

[FIXED] How can I pull only 'yesterday's' orders from Shopify API using Python. Needs to be dynamic, not static

Issue

I have successfully pulled all orders. However, my goal is to have my task scheduler run my python script to pull yesterday's or today's only orders and append the orders to an ongoing master orders list in either a SQL table or excel file. I am thinking it has to be a parameter in the URL but can't figure it out. Can anyone help with this? below is my python script URL:

date = datetime.today().strftime('%Y-%m-%d')
url = "https://{API}:{Password}@{StoreName}/admin/api/2021-10/orders.json?created_at=" + date + f"&limit=250&status=any&since_id={last}"
response = requests.request("GET", URL)

Solution

According to the shopify documentation here about orders you can give 2 parameters:

created_at_max (Show orders created at or before date.) & created_at_min (Show orders created at or after date.)

So if you try these in your setup you should be fine. Example from the docs:

curl -X GET "https://your-development-store.myshopify.com/admin/api/2021-10/orders.json?updated_at_min=2005-07-31T15%3A57%3A11-04%3A00"

Answered By - JustLudo

Answer Checked By - Mildred Charles (PHPFixing Admin)

Read More

[FIXED] How to calculate the difference between two dates using PHP?

Issue

I have two dates of the form:

Start Date: 2007-03-24 
End Date: 2009-06-26

Now I need to find the difference between these two in the following form:

2 years, 3 months and 2 days

How can I do this in PHP?

Solution

Use this for legacy code (PHP < 5.3). For up to date solution see jurka's answer below

You can use strtotime() to convert two dates to unix time and then calculate the number of seconds between them. From this it's rather easy to calculate different time periods.

$date1 = "2007-03-24";
$date2 = "2009-06-26";

$diff = abs(strtotime($date2) - strtotime($date1));

$years = floor($diff / (365*60*60*24));
$months = floor(($diff - $years * 365*60*60*24) / (30*60*60*24));
$days = floor(($diff - $years * 365*60*60*24 - $months*30*60*60*24)/ (60*60*24));

printf("%d years, %d months, %d days\n", $years, $months, $days);

Edit: Obviously the preferred way of doing this is like described by jurka below. My code is generally only recommended if you don't have PHP 5.3 or better.

Several people in the comments have pointed out that the code above is only an approximation. I still believe that for most purposes that's fine, since the usage of a range is more to provide a sense of how much time has passed or remains rather than to provide precision - if you want to do that, just output the date.

Despite all that, I've decided to address the complaints. If you truly need an exact range but haven't got access to PHP 5.3, use the code below (it should work in PHP 4 as well). This is a direct port of the code that PHP uses internally to calculate ranges, with the exception that it doesn't take daylight savings time into account. That means that it's off by an hour at most, but except for that it should be correct.

<?php

/**
 * Calculate differences between two dates with precise semantics. Based on PHPs DateTime::diff()
 * implementation by Derick Rethans. Ported to PHP by Emil H, 2011-05-02. No rights reserved.
 * 
 * See here for original code:
 * http://svn.php.net/viewvc/php/php-src/trunk/ext/date/lib/tm2unixtime.c?revision=302890&view=markup
 * http://svn.php.net/viewvc/php/php-src/trunk/ext/date/lib/interval.c?revision=298973&view=markup
 */

function _date_range_limit($start, $end, $adj, $a, $b, $result)
{
    if ($result[$a] < $start) {
        $result[$b] -= intval(($start - $result[$a] - 1) / $adj) + 1;
        $result[$a] += $adj * intval(($start - $result[$a] - 1) / $adj + 1);
    }

    if ($result[$a] >= $end) {
        $result[$b] += intval($result[$a] / $adj);
        $result[$a] -= $adj * intval($result[$a] / $adj);
    }

    return $result;
}

function _date_range_limit_days($base, $result)
{
    $days_in_month_leap = array(31, 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);
    $days_in_month = array(31, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);

    _date_range_limit(1, 13, 12, "m", "y", &$base);

    $year = $base["y"];
    $month = $base["m"];

    if (!$result["invert"]) {
        while ($result["d"] < 0) {
            $month--;
            if ($month < 1) {
                $month += 12;
                $year--;
            }

            $leapyear = $year % 400 == 0 || ($year % 100 != 0 && $year % 4 == 0);
            $days = $leapyear ? $days_in_month_leap[$month] : $days_in_month[$month];

            $result["d"] += $days;
            $result["m"]--;
        }
    } else {
        while ($result["d"] < 0) {
            $leapyear = $year % 400 == 0 || ($year % 100 != 0 && $year % 4 == 0);
            $days = $leapyear ? $days_in_month_leap[$month] : $days_in_month[$month];

            $result["d"] += $days;
            $result["m"]--;

            $month++;
            if ($month > 12) {
                $month -= 12;
                $year++;
            }
        }
    }

    return $result;
}

function _date_normalize($base, $result)
{
    $result = _date_range_limit(0, 60, 60, "s", "i", $result);
    $result = _date_range_limit(0, 60, 60, "i", "h", $result);
    $result = _date_range_limit(0, 24, 24, "h", "d", $result);
    $result = _date_range_limit(0, 12, 12, "m", "y", $result);

    $result = _date_range_limit_days(&$base, &$result);

    $result = _date_range_limit(0, 12, 12, "m", "y", $result);

    return $result;
}

/**
 * Accepts two unix timestamps.
 */
function _date_diff($one, $two)
{
    $invert = false;
    if ($one > $two) {
        list($one, $two) = array($two, $one);
        $invert = true;
    }

    $key = array("y", "m", "d", "h", "i", "s");
    $a = array_combine($key, array_map("intval", explode(" ", date("Y m d H i s", $one))));
    $b = array_combine($key, array_map("intval", explode(" ", date("Y m d H i s", $two))));

    $result = array();
    $result["y"] = $b["y"] - $a["y"];
    $result["m"] = $b["m"] - $a["m"];
    $result["d"] = $b["d"] - $a["d"];
    $result["h"] = $b["h"] - $a["h"];
    $result["i"] = $b["i"] - $a["i"];
    $result["s"] = $b["s"] - $a["s"];
    $result["invert"] = $invert ? 1 : 0;
    $result["days"] = intval(abs(($one - $two)/86400));

    if ($invert) {
        _date_normalize(&$a, &$result);
    } else {
        _date_normalize(&$b, &$result);
    }

    return $result;
}

$date = "1986-11-10 19:37:22";

print_r(_date_diff(strtotime($date), time()));
print_r(_date_diff(time(), strtotime($date)));

Answered By - Emil H

Answer Checked By - David Goodson (PHPFixing Volunteer)

Read More

[FIXED] How to compare datetime field from Doctrine2 with a date?

Issue

I want to get the items which were created today with by a QueryBuilder from Doctrine2. I want to compare the createdAt(Datetime) field with today parameter(Date). Is it possible to do that in one query?

$qb = $this->createQueryBuilder('i');
$qb->innerJoin('i.type', 'it');
$qb->andWhere('it.name = :type');
$qb->andWhere('i.createdAt < :today');
// i.createdAt == datetime and :today parameter is a date

Solution

one idea is to extract from the date: the year, month and day. And then

$qb->select('p')
   ->where('YEAR(p.postDate) = :year')
   ->andWhere('MONTH(p.postDate) = :month')
   ->andWhere('DAY(p.postDate) = :day');

$qb->setParameter('year', $year)
   ->setParameter('month', $month)
   ->setParameter('day', $day);

MONTH DAY, and YEAR you take out the DoctrineExtensions from

e.g.

DoctrineExtensions

This works for me. You only need the files: day.php, month.php and year.php.....

You get the month e.g.:

    $datetime = new \DateTime("now");
    $month = $datetime->format('m');
    echo $month;

Copy day.php, month.php and year.php to your bundle Xy\TestBundle\Dql Register the new functions in app\config.yml with

doctrine:


orm:
    auto_generate_proxy_classes: %kernel.debug%
    entity_managers:
        default:
            auto_mapping: true
            dql:
                datetime_functions:
                    month: Xy\TestBundle\Dql\Month
                    year: Xy\TestBundle\Dql\Year
                    day: Xy\TestBundle\Dql\Day

Answered By - stwe

Answer Checked By - David Goodson (PHPFixing Volunteer)

Read More

[FIXED] How to properly set "0000-00-00 00:00:00" as a DateTime in PHP

Issue

I have a column viewedAt which is a DATETIME and accept NULL values. It's a software restriction to set that column on each new record as 0000-00-00 00:00:00 so I go through the easy way using Symfony and Doctrine as show below:

$entEmail = new Email();
$entEmail->setViewedAt(new \DateTime('0000-00-00 00:00:00'));

But surprise PHP change that date to this -0001-11-30 00:00:00 and SQL mode in MySQL server is set to STRICT so query fails. I have read a lof of topics here as this, this and some other but didn't answer my doubt at all. I have made a few test with PHP alone and I got almost the same result:

$date = new DateTime("0000-00-00 00:00:00", new DateTimeZone('America/New_York'));
echo $date->format('Y-m-d h:i:s');

// outputs
// -0001-11-30 12:00:00

Even with MySQL mode set to STRICT I did test the following query and it works as image below shows:

INSERT INTO emails(`emails_category`, `deliveredAt`, `viewedAt`, `data`, `createdAt`, `updatedAt`, `reps_id`, `targets_id`) VALUES ("sent","2015-10-29 06:08:25","0000-00-00 00:00:00",null,"2015-10-29 06:08:25","2015-10-29 06:08:25","005800000058eYcAAI","0018000001GO8omAAD")

So viewedAt is accepting 0000-00-00 00:00:00 that value even if is not valid (I think it's kind of NULL or so)

How I can fix this? Is there any work around? What did you suggest me on this specific case where this is a software requirement and can't be changed?

I'm using Symfony 2.7.6, Doctrine 2.5.2 and PHP 5.5.30

Solution

Your architecture is wrong to begin with. The problem is not setting the date itself, which is so obviously invalid that both MySQL and PHP are right to reject it, as there is no year 0 and no day 0 of a month 0, and the output you see is just the correction to a sort-of-valid date (it's 1 year, 1 month and 1 day before 01/01/01). But you're also just missing the point that Doctrine abstracts this away if you just do it right:

$entEmail = new Email();
$entEmail->setViewedAt(null);

Doctrine will now happily put NULL in the database column as it should be.

Answered By - Niels Keurentjes

Answer Checked By - David Marino (PHPFixing Volunteer)

Read More